SQL Insert Multiple Rows

“Hi, fellow geeks. In this article, we will focus on learning how to insert multiple rows across multiple database engines, including MySQL, PostgreSQL, and SQL Server.

Database operations are mainly comprised of Create, Read, Update, and Delete. Therefore, learning how to insert records into a database is fundamental.”

SQL Insert Statement

SQL uses the insert statement to insert a record or multiple records into a table. The statement syntax is as shown below:

INSERT INTO TABLE_NAME (col1, col2, col3)
VALUES (value1, value2, value3, ...);

Let’s see how we can use this statement to insert records into a table.

SQL Insert Multiple Rows – MySQL

The first database we will cover is the MySQL engine. It is one of the most popular relational database systems. It provides a simple, intuitive syntax while sticking to the standard SQL syntax.

Before proceeding, ensure you have the MySQL Server installed and running in your system.

Let’s start by creating a sample database we can use.

Run the code:

CREATE DATABASE simple_shop;

Once the command is executed, we can switch to the database we created with the command:

USE simple_shop;

Next, we need to create a table schema with the columns and the target data types. An example schema is provided in the code below.

CREATE TABLE products (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
price_per_item INT,
stock INT NOT NULL,
supported BOOL NOT NULL
);

In the code above, we create a table called products. It holds four columns containing product information.

Feel free to customize the table schema or create your sample on which to work.

Once the table schema is created, we can insert the records into the table.

To insert a single row at a time, we can use multiple insert statements as shown:

INSERT INTO products (product_name, price_per_item, stock, supported)
VALUES('Wireless Earbuds', 59.23, 100, TRUE);
INSERT INTO products (product_name, price_per_item, stock, supported)
VALUES('Energy Drinks', 5.0, 500, TRUE);
INSERT INTO products (product_name, price_per_item, stock, supported)
VALUES('Scouts Knife', 129.35, 70, FALSE);

In the code above, we use three separate inserts three rows into the table.

We can verify the data is inserted into the table by running the query:

SELECT
*
FROM
products;

The output should return a table with the insert rows.

As we can see, the table contains the records we inserted previously.

Although the above method works, it is repetitive and requires us to use multiple insert statements.

We can resolve this by using the provided method of inserting multiple statements. An example is as shown:

INSERT INTO products(product_name, price_per_item, stock, supported)
VALUES('Kitchen Faucet,'
24.65,
450,
TRUE),
('Trampoline',
56.44,
30,
FALSE),
('Smart Watch,'
544,
100,
TRUE),
('Tempered Glass,'
56.99,
1000,
TRUE),
('Wireless Charger',
96.88,
22,
TRUE);

Here, we specify the target table and the columns we wish to insert. We then provide multiple values in a pair of parentheses separated by a comma. Doing this allows us to specify a single insert statement and include all the values we wish to insert, one after the other.

We can now query the table:

We can see that the records are added to the table.

SQL Insert Multiple Rows – PostgreSQL

Let us look at how to insert multiple records into PostgreSQL. Thankfully, the syntax does not differ at all from the one provided by MySQL.

Let us start by creating our sample database in PostgreSQL.

CREATE SCHEMA simple_shop;

We can switch the database as:

SET search_path = 'simple_shop';

We can now create a table schema as provided in the code below:

CREATE TABLE simple_shop.products (id SERIAL PRIMARY KEY,
product_name VARCHAR(255)
NOT NULL,
price_per_item MONEY,
stock INT NOT NULL,
supported BOOL NOT NULL);

To insert multiple records in PostgreSQL, we can do the following:

INSERT INTO products (product_name, price_per_item, stock, supported)
VALUES('Kitchen Faucet', 24.65, 450, TRUE), ('Trampoline', 56.44, 30, FALSE), ('Smart Watch', 544, 100, TRUE), ('Tempered Glass', 56.99, 1000, TRUE), ('Wireless Charger', 96.88, 22, TRUE);

Similar to MySQL, we can insert multiple records by adding by closing them in a pair of parentheses and separating them with commas in an INSERT statement.

SQL Insert Multiple Rows – SQL Server

The good thing about SQL Server is that it does not deviate much from Standard SQL. Therefore, we can use a similar syntax as MySQL to insert multiple records.

For example, the same insert statement should work on SQL Server.

The command should insert the specified records into the table.

Terminate

In this tutorial, we discussed how to work with database tables and insert statements. We learned how to insert single or multiple rows into a table in various database engines.

Thanks for reading!!