How to insert multiple rows with SQL

Problem

You have multiple sets of data that you need to insert into a SQL table at the same time.

Sample data

Assume we have a table called Employees with columns EmployeeID, FirstName, LastName, and Email.

MySQL Solution

INSERT INTO Employees (EmployeeID, FirstName, LastName, Email)
VALUES 
(1, 'John', 'Doe', '[email protected]'),
(2, 'Jane', 'Doe', '[email protected]'),
(3, 'Jim', 'Doe', '[email protected]');

PostgreSQL Solution

INSERT INTO Employees (EmployeeID, FirstName, LastName, Email)
VALUES 
(1, 'John', 'Doe', '[email protected]'),
(2, 'Jane', 'Doe', '[email protected]'),
(3, 'Jim', 'Doe', '[email protected]');

Explanation

In SQL, you can insert multiple rows into a table using a single INSERT INTO statement.

The syntax for this in both MySQL and PostgreSQL is the same. You start with the INSERT INTO keyword followed by the table name. In parentheses, you specify the column names where you want to insert data. After the VALUES keyword, you provide the data for the rows you want to insert, with each row's data enclosed in parentheses and separated by commas.

The above code inserts three new rows into the Employees table. Each row's data is specified in a separate set of parentheses.

Please note that the order of values you are inserting should match the order of column names you specified after INSERT INTO keyword. For example, if you specified (EmployeeID, FirstName, LastName, Email), the values should be provided in the same order like (1, 'John', 'Doe', '[email protected]').

In case you are inserting values into all columns of the table and in the same order as the columns exist in the table, you can omit the column names:

INSERT INTO Employees
VALUES 
(1, 'John', 'Doe', '[email protected]'),
(2, 'Jane', 'Doe', '[email protected]'),
(3, 'Jim', 'Doe', '[email protected]');

This will work for both MySQL and PostgreSQL.