INSERT vs LOAD DATA INFILE in MySQL: When to Choose Each for Speed and Bulk Loads

Introduction

The article explains the differences between MySQL's INSERT INTO statement and the LOAD DATA INFILE statement, and when to use both for data import.

INSERT Statement

Basic Syntax

The simplest form is: INSERT INTO demo_table (column_1) VALUES ('Demo Data'); The statement consists of:

INSERT INTO – tells MySQL to perform an INSERT operation.

demo_table – the target table name.

column_1 – the column to receive the data.

VALUES – introduces the values to be inserted.

'Demo Data' – the literal value for column_1.

Examples

INSERT INTO arctype (column, column_2) VALUES ('Demo', 'Demo 2');

Multiple rows can be inserted in a single statement:

INSERT INTO arctype (demo_1, demo_2) VALUES ('Demo Data', 'Demo 2'),('Demo Data Again', 'Data123'),('Data here', 'Data here too');

If the values match the column order, the column list can be omitted:

INSERT INTO arctype VALUES ('Demo', 'Demo 2');

Common Pitfalls

Values must match the column data types. For example, inserting a string into an INT column causes error #1366:

#1366 - Incorrect integer value: 'Demo' for column 'number_column' at row 1