Skip to main content
SimilarExport ZIP
100%

SQL to upload the two big MOT data files to a MySQL database, requires the tables to by using the MyIASM engine.

✓ Published0🌍 Public
IIanHopkinson
Last edited Jul 15, 2013
Created on Jul 15, 2013

This example demonstrates a SQL script that loads two large MOT test data files into a MySQL database. It first disables keys on the `testitem` and `testresult` tables, then uses `LOAD DATA LOCAL INFILE` to bulk-import tab-delimited text files, and finally re-enables keys to speed up the insertion. The code relies on MySQL’s `ALTER TABLE ... DISABLE KEYS` and `ENABLE KEYS` directives to optimize index maintenance for the MyISAM storage engine. The data source consists of two plain-text files, `UAT_test_item_2011.txt` and `UAT_test_result_2011.txt`, with fields separated by pipes, and the path is specified by the user.

AI-generated description

Similar vizzes

Loading thumbnail…

SQL to upload the smaller tables of the MOT test dataset

This example demonstrates how to prepare smaller reference tables from the MOT test dataset for database loading, showing the SQL commands that import three text files—failure_location, item_detail, and item_group—into their corresponding MySQL tables. The script uses the `LOAD DATA LOCAL INFILE` API to parse pipe-delimited files with newline-terminated records, explicitly specifying field and line terminators. The code relies on standard SQL syntax within a MySQL environment, first selecting the MOT database with `USE MOT` before executing each import. This approach highlights a straightforward, file-based data ingestion pattern rather than a visual rendering technique, focusing on the database population step that precedes any charting or visualization work.

IIanHopkinson
81% match
Loading thumbnail…

A schema for uploading MOT test data, as suggested in the user guide with the exception of the `SET storage_engine=MyISAM` which is there to allow the `ENABLE KEYS\DISABLE KEYS` mechanism used in the large table uploads. Also the field `RFRINSPMANDESC TEXT(500) #Variation from suggested schema due to 1074 error` is suggested as `char` in the user guide but for my MySQL setup this exceeds the allowed size for char.

This schema defines the relational structure for importing UK MOT test data, showing the primary tables for test results, test items, item details, item groups, and failure locations. It demonstrates the use of MySQL DDL with explicit primary keys, composite indexes, and the MyISAM storage engine to enable bulk-loading optimizations. The code highlights practical deviations from the official user guide, such as using `TEXT(500)` for inspection descriptions instead of an oversized `CHAR`, accommodating MySQL’s row-size limits while preserving data integrity.

IIanHopkinson
73% match
Loading thumbnail…

My simply MySQL Command Line Cheatsheet

This example displays a plain-text cheat sheet of essential MySQL command-line operations, organized into sections for database and table management, querying, aggregate functions, and multi-table joins. The gist presents over forty commands with syntax templates, usage notes, and links to external tutorials and tools like DataGrip and Sequel Pro. It also includes configuration snippets for `max_allowed_packet` and a bash alias for MAMP’s MySQL binary. The content is sourced from a GitHub gist by user Kirun1.

KKirun1
50% match
Loading thumbnail…

Concept for inserting data into SQL Server

This example demonstrates a concept for inserting data into SQL Server by using Power Query’s M language. It shows a table creation script for a demo table with an identity column, a key, and a value, followed by an M query that iterates over a list of key-value pairs. The code uses `List.Accumulate` to loop through the pairs and calls `OleDb.DataSource` with a SQL `INSERT` statement for each iteration, referencing the SQLOLEDB provider to connect to a local database. The result is the total number of affected records from the inserts, though the actual inserted rows are not visualized or returned in the output.

HHugoberry
48% match
Loading thumbnail…

Power Query export data to SQL Server

This example demonstrates exporting data from a SQL Server database by running a large SQL script stored in a local file. It shows how Power Query can leverage native database connectivity to execute a bulk insert operation with high parallelism. The code uses `File.Contents` to read the SQL file, then passes its binary content to `Value.NativeQuery`, which targets a SQL Server instance via `Sql.Database`. Key parameters include `EnableBulkInsert` for efficient data loading, `MaxDegreeOfParallelism` to speed up execution, and explicit timeouts for command and connection. The rendered output is the result set from the query, effectively transferring data into Power BI’s data model.

HHugoberry
47% match
Loading thumbnail…

Gist e9eac02df35654daa5d0

This example demonstrates a minimal data pipeline that reads JSON data and inserts it into a local SQLite database. The script, `jsonToSqlite.py`, loads a JSON file containing a list of objects, extracts the first object’s `"foo"` and `"bar"` fields, and writes those two values as a single row into a table named `table_name`. It uses the standard `json` module for parsing and `sqlite3` for database operations, creating a table and inserting the data with a parameterized `execute` call. The visualization shows only the code structure, not a graphical output, highlighting the simple ETL pattern of extracting, transforming, and loading data.

CCBasis
46% match
Loading thumbnail…

Gist faec7eb6c03589ff608bab991699407b

This example demonstrates how to generate and execute a dynamic SQL script in Power Query (M language) to bulk-insert data into a SQL Server table using a table-valued parameter (TVP). It builds a textual TVP declaration and populates it by chunking source rows into sets of 1,000 entries, then constructs a multi-statement script that inserts each chunk and calls a stored procedure. The code relies on M functions like `Table.TransformRows`, `List.Generate`, `Text.Combine`, and `Value.NativeQuery` to programmatically assemble and run the query. The visualization highlights the data flow from a source table to a SQL stored procedure, emphasizing the chunking logic and the use of native query execution.

HHugoberry
46% match
Loading thumbnail…

Created on Cognitive Class Labs

This example demonstrates how to load three Chicago public datasets—socioeconomic indicators, public school performance, and crime reports—from CSV files into a Db2 database using the `ibm_db` API, then query them with SQL. It uses the `pandas` library to read the CSV data and the `ibm_db` connection functions to create and populate tables. The notebook guides through understanding the datasets, downloading them, and storing them in database tables, with the SQL queries highlighting relationships between community areas, schools, and crime incidents.

DDAVINDER
45% match