SQL LOAD

What does the SQL LOAD statement do?

Loads bulk data from an external file directly into a database table.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL LOAD: MySQL, MariaDB (native); Snowflake (LOAD), Redshift (COPY), SQL Server (BULK INSERT) offer similar functionality; PostgreSQL uses COPY instead.

SQL LOAD Full Explanation

The SQL LOAD statement (most commonly LOAD DATA INFILE in MySQL and MariaDB) is a high-performance, server-side mechanism for ingesting large datasets stored in text or CSV files into an existing table. Unlike iterative INSERT statements, LOAD parses the source file in a streaming fashion and commits the rows in batches, drastically reducing network overhead and transaction time. Options let you define character sets, field and line delimiters, selective column mapping, and on-the-fly transformations via SET expressions. Because the command executes on the database server, the file path must be accessible to the server OS unless the LOCAL keyword is used, which streams the file from the client. File permissions, secure-file-priv restrictions, and column type mismatches are common pitfalls to check before running.

SQL LOAD Syntax

LOAD DATA [LOCAL] INFILE 'file_path'
INTO TABLE table_name
[CHARACTER SET charset_name]
[FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"']
[LINES TERMINATED BY '\n']
[(column1, column2, ...)]
[SET columnN = expression];

SQL LOAD Parameters

file_path (string) - Absolute or relative path to the source file
LOCAL (keyword) - Streams file from client instead of server file system
charset_name (string) - Character set used in the file (e.g., utf8mb4)
FIELDS TERMINATED BY (string) - Field delimiter, often a comma or tab
OPTIONALLY ENCLOSED BY (string) - Text qualifier, usually a double quote
LINES TERMINATED BY (string) - Line break sequence (e.g., \n, \r\n)
(column list) (identifier list) - Optional order of columns to import
SET clause (expression) - Assigns computed values to columns during load

Example Queries Using SQL LOAD

-- Basic CSV load
LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(id, first_name, last_name, email);

-- Load from client machine with charset conversion and default timestamp
LOAD DATA LOCAL INFILE '~/downloads/orders.tsv'
INTO TABLE orders
CHARACTER SET utf8mb4
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
(order_id, customer_id, amount)
SET created_at = NOW();

Expected Output Using SQL LOAD

The server parses the file and inserts each valid row into the target table
Upon completion it returns a message such as: "Query OK, 10,000 rows affected (0
45 sec)
"

Use Cases with SQL LOAD

Initial population of dimension or reference tables
One-off backfills from exports or flat-file feeds
Nightly batch imports from upstream systems
Rapid migration of CSV data created by spreadsheets

Common Mistakes with SQL LOAD

Pointing to a file path the server process cannot access
Forgetting the LOCAL keyword when the file resides on the client
Mismatching field or line delimiters, causing all data to load into the first column
Loading into a table with NOT NULL columns not present in the column list
Ignoring secure-file-priv restrictions that block server-side file access

First Introduced In

MySQL 3.23

Frequently Asked Questions

How do I skip the header row?

Add `IGNORE 1 LINES` after the `LINES TERMINATED BY` clause so MySQL discards the first record.

Can I load only specific columns?

Yes. List the desired columns in parentheses after the table name. Columns omitted receive their default values or NULL.

Is LOAD DATA INFILE transactional?

If the target table is InnoDB and the command runs inside an explicit transaction, it can be rolled back. Otherwise, rows are committed immediately.

How can I speed up very large imports?

Temporarily disable indexes and foreign keys, increase `bulk_insert_buffer_size`, and wrap the load in a single transaction.