Inserting Multiple Rows

Oftentimes we need to insert more than one row into a table, and re-writing the insert statements many times can be tedious and inefficient. Luckily, SQL provides us with a slightly shorter syntax which basically compresses multiple INSERT statements into a single query.

Following is the general syntax for inserting multiple rows using a single insert statement:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES 
    (value1_1, value1_2, value1_3, ...),
    (value2_1, value2_2, value2_3, ...),
    (value3_1, value3_2, value3_3, ...),
    ...;

The following example shows the syntax for inserting multiple rows into a table called students which has three columns first_name, second_name and age:

Tip:

This syntax has a shorter version as well, in which we don't specify the column names:

INSERT INTO table_name
VALUES 
    (value1_1, value1_2, value1_3, ...),
    (value2_1, value2_2, value2_3, ...),
    (value3_1, value3_2, value3_3, ...),
    …;

In this case the query will look like this:

INSERT INTO students
VALUES
    ('Alice', 'Smith', 20),
    ('Bob', 'Johnson', 22),
    ('Charlie', 'Brown', 19);

SELECT * FROM students;

Demonstration: