SQL Insert Multiple Rows – All Possible Ways

In this SQL Tutorial, you will learn How to insert multiple rows using SQL?

It is completely possible with SQL to insert multiple rows into a table. We’ll see all the different solutions with SQL to Insert multiple records, whether you are using MySQL server database, MS SQL server database or anyone else.

Here is how you can do it-

The SQL INSERT command can be used to insert one or multiple records into one or multiple tables in a single query. Generally, the INSERT statement is used to insert single record by following the pattern-

INSERT INTO table_name (comma_separated_column_list)
VALUES (comma_separated_values);

As you can see in the above syntax, we provide the required names of columns to be filled with corresponding values specified after the VALUES command.

Let’s have a look- how to insert multiple rows in SQL in One time?

Example 1: SQL Insert Multiple Rows

Using MySQL 

Step 1) Create a Sample Database and Table

First and foremost, we should have at least one database and a table that has a couple of records in it. We will later use this table to insert multiple rows in another table at one time.

SQL Query: to create a sample database and Table in MySQL-

CREATE DATABASE db_sql;

CREATE TABLE tbl_age(
   age int(3)
);

Step 2) Insert dummy records in First Table

We must have some records in it so that we’ll copy them into another table.

SQL Query: to insert sample records in it-

INSERT INTO tbl_age values( 30 );
INSERT INTO tbl_age values( 60 );
INSERT INTO tbl_age values( 90 );
INSERT INTO tbl_age values( 60 );
INSERT INTO tbl_age values( 30 );
INSERT INTO tbl_age values( 44 );
INSERT INTO tbl_age values( 28 );
INSERT INTO tbl_age values( 52 );

Now, we have a table ‘tbl_age‘ with 8 records with some duplicates values.

Step 3) Create a Table to Store multiple rows

CREATE TABLE tbl_young_age(
   age int(2)
);

Step 4) MySQL-Insert Multiple Rows in Table

Now, let’s say, we want to store all ‘age‘s into ‘tbl_young_age‘ table that are greater than 10 years and less than 50 years in a single query. We can do it by executing the below SQL query-

INSERT INTO tbl_young_age( age ) 
	SELECT DISTINCT age 
	FROM tbl_age
	WHERE age > 10 and age <50;

What’s happening here is, before the actual insertion operation, the SELECT query would be executed completely and fetch multiple rows according to the WHERE clause.

Once, the SELECT operation has been done, the INSERT operation will get executed and insert the fetched rows into the second table ‘tbl_young_age‘. This is how- SQL query will insert multiple rows into the table ‘tbl_young_age‘.

Below is an output screenshot of the final result-

2) MySQL: Insert Multiple Rows in Same Table

In the above example, if you’ve noticed at step 2), we’re inserting 8 rows in table ‘tbl_age’ using 8 different SQL Queries.

Here is another approach that you can follow to insert multiple rows in one SQL Query within the same Table-

INSERT INTO tbl_age (age)
values (30),
values (60),
values (90),
values (60),
values (30),
values (44),
values (28),
values (52);		-- Here's, the Single Query is ending.

Example 2: MS SQL Insert Multiple Rows

Using MS SQL 

In this section, we’ll learn how to insert multiple rows using Microsoft SQL Server database.

One simplest approach would be to use the above MySQL Single Query example. Here we will take an example with Cinema house.

Let’s say, for a Cinema house software, we have a table ‘Movies’ that has a unique ID, a Name, Price, Release date and End date. Now we want to insert a number of rows to test few other things.

Here’s how we’ll do that-

Step 1) Create the Movies table

CREATE TABLE tbl_movies (
    movie_id INT PRIMARY KEY IDENTITY (1,1),
    movie_name VARCHAR (255) NOT NULL,
    price NUMERIC (5, 2) DEFAULT 0,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL
);

In the above SQL query, we have created a table named ‘tbl_movies‘ in the default database of Microsoft SQL Server database.

Step 2) Fire SQL to Insert multiple rows in Table

INSERT INTO tbl_movies (
    movie_name,
    price,
    start_date,
    end_date
)
VALUES
    (
        'ShubhamKLogic.com: A Tech Biography',
        30.00,
        '20210101',
        '20310101'
    ),                 -- First row
    (
        'Avengers: End Game',
        25.50,
        '20200422',
        '20201126'
    ),                 -- Second row
    (
        'Dangal',
        28.50,
        '20180201',
        '20181201'
    ),                 -- Third row
	(
        'MS Dhoni: An Untold Story',
        28.50,
        '20190123',
        '20190422'
    ),                 -- Fourth row
	(
        'Life of Pie',
        22.50,
        '20160901',
        '20170101'
    );                 -- Last row, the Single SQL Query ends here...

In the above SQL query, we’ve tried to insert multiple rows into the MS SQL table ‘tbl_movies‘. What makes it different from inserting a single row is, all the records are separated by a comma , which allows to insert multiple rows successfully.

Step 3) Verify the records

Here is the output screenshot of the above SQL query execution-

What We Learned? 

In this SQL Tutorial, we’ve learned- How to insert multiple rows in SQL at a time? We’ve also learned: SQL INSERT multiple rows from another table.

In the above examples, we learned different ways to insert multiple rows in SQL. Finally, we had prepared and run the SQL query for 2 different databases –

Hope, you found this SQL tutorial helpful. If you have any doubt or queries, feel free to comment below or submit your question via Google, I’ll definitely try to give you an answer within a day.

See Related Popular SQL Articles that you may like:

• Practice SQL Query Interview Questions – For Beginners
• Master SQL Query Interview Questions – For Intermediate
• Top mistakes that every Modern programmer does