How to INSERT multiple rows in SQL

Overview: In this tutorial you will learn and understand, how to insert multiple rows in SQL table directly from query and from one SQL table to another SQL table.

Introduction: INSERT multiple rows in SQL

You can INSERT multiple rows in SQL via using INSERT INTO command. With this command you can insert multiple rows/records direct from SQL query and also you can insert multiple rows/records in a table from another existing table in SQL.

Suppose below image1 is the Employee table which is blank

Image1

and after applied INSERT INTO command, then new inserted rows will look like below table image2

Lets understand the syntax.

Syntax 1: INSERT rows values directly from SQL query

INSERT INTO 
        Table_name (column1, Column2, column3, column4)
VALUES
    (value1, value2, value3, value4),
    (value5, value6, value7, value8),
    ...
    (value9, value10, value11, value12),

Syntax 2: INSERT multiple rows from another SQL table

INSERT INTO Target_table_name (column_list) 
      SELECT (column_list) 
      FROM  Anothet_SQL_table_name     //SELECT query, bring data from another SQL table

Note:
Select command in above syntax is used to say that you need to apply another SELECT command which will bring rows from another existing SQL table.

Let’s understand with 5 best examples, which will clarify your doubt easily.

Example #1: INSERT multiple rows values directly from SQL query

In below example, we have used 4 rows values as per columns. These values as per the data types of the column, and in this example we are inserting rows values in each columns which is new record, Here we are not bringing any records values from another SQL table.

This way of inserting rows or records is helpful when you need to insert few numbers of rows in one go.

INSERT INTO Orders_Tbl
           ([Order_ID], [Order_Date], [Customer_ID], [Order_Priority], [Order_Quantity], Sales, Discount)
VALUES
    ('1250', '2011-10-21', '8748', 'Not Specified', '35', '200.56', '0.03'),
    ('1251', '2011-10-22', '8749', 'Not Specified', '36', '300.56', '0.03'),
    ('1251', '2011-10-23', '8750', 'Not Specified', '37', '400.56', '0.03'),
    ('1252', '2011-10-24', '8751', 'Not Specified', '38', '500.56', '0.03')

In below result it will show how many rows you have inserted in table, if you want to se exact result you need to run Select command to show data or rows

Result:

Example #2: Inserting individual rows into the table

In below example, you can also use to insert rows values individual. This method also work but its totally depend on you which look okay for you.

However our recommendation is to use example 1 if you want to insert multiple rows, because that will save your code text and time both.

INSERT INTO Orders_Tbl VALUES('1250', '2011-10-21', '8740', 'Not Specified', '35');

INSERT INTO Orders_Tbl VALUES('1251', '2011-11-21', '8741', 'Not Specified', '36');

INSERT INTO Orders_Tbl VALUES('1252', '2011-12-21', '8742', 'Not Specified', '37');

Example #3: INSERT multiple records from another existing SQL table

In this below example, we are trying to insert data from existing SQL table. So lets consider “Orders_Tbl_temp“ table in which we would like to insert data from existing SQL table is “Orders_Tbl”

we explained 2 different methods can be used to INSERT multiple rows using INSERT command in SQL

Method 1: Insert all columns from one SQL table to another SQL table

INSERT INTO 
       [Orders_Tbl_Temp]
  ( 
     SELECT 
           * 
     FROM 
          Orders_Tbl 
   )

Result:

In this above example all columns from SQL table [Orders_Tbl ] got inserted into existing SQL table [Orders_Tbl_Temp]. However, it is possible only if both table have the same numbers of columns and sequence of columns.

Note:
If number of columns in both tables are different then it will through the error

Method 2: Insert specific columns from one SQL table to another SQL table

INSERT INTO [Orders_Tbl_Temp]
           ([Order_ID], [Customer_ID], [Order_Quantity], [Discount])
    (
      SELECT 
           [Order_ID], [Customer_ID], [Order_Quantity], [Discount]
      FROM 
           Orders_Tbl 
     )

In the above example, SQL query is trying to insert the only 4 columns from existing SQL table into targeted table, The best part of this way of inserting table is that, there is no need to have same numbers of columns and sequence of columns in 2 tables.

with this way of inserting multiple rows is really help to keep the flexibility and any change in the SQL tables columns in future, this Query will manages the changes easily.

Example #4: INSERT multiple records from another existing SQL table Using Conditions

In the below example, we are trying to insert the rows from another table with condition using WHERE clause. This is important when you wan to insert rows for specific data from existing SQL table.

INSERT INTO [Orders_Tbl_Temp]
            ([Order_ID], [Customer_ID], [Order_Quantity], [Discount])
            
      (
         SELECT 
                [Order_ID], [Customer_ID], [Order_Quantity], [Discount]
         FROM 
                Orders_Tbl  
         WHERE 
                Customer_ID] = '8750'
        )

Note:
Remember when you are trying to insert multiple rows from one table to existing targeted table using condition then try to use GROUP BY clause, because it will group the rows and reduce the size of inserted rows in the table.

Example #5: INSERT multiple records from another table Using UNION ALL

By Using UNION all command you can merge data from two table into one table as shown below example, We are inserting multiple rows in [Orders_Tbl_Temp] table from Orders_Tbl_1 and Orders_Tbl_2

INSERT INTO [Orders_Tbl_Temp]
            ([Order_ID], [Customer_ID], [Order_Quantity], [Discount])
            
      (
         SELECT 
                [Order_ID], [Customer_ID], [Order_Quantity], [Discount]
         FROM 
                Orders_Tbl_1  

        UNION ALL
         
        SELECT 
                [Order_ID], [Customer_ID], [Order_Quantity], Discount]
         FROM 
                Orders_Tbl_2

        )

Conclusion:

Insert multiple rows in table is the powerful way to update your table with new data. You can use any example based on your need. Inserting rows and records in table is needed and you can also add this in your procedure to make it automated.