Insert Multiple Values in Table Using Single Query in SQL Server 2012

Today, I have provided an article showing you how to insert multiple values into a table using a single query in SQL Server 2012. This is an interesting question. Many new developers write insert queries again and again to insert multiple records into a table. There is no need to use multiple insert queries; you can do it using a single query. There are two ways to do it. First we can use a simple insert query and another way is to use a union all to do it. Here, I have a table named EmployeeDetail with the columns emp_fname, emp_lname, emp_no, emp_add. I have constructed a query that inserts multiple values in table. Let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

The table looks as in the following:

1. Insert multiple values in table using single INSERT Query

The syntax for inserting multiple values is:

INSERT INTO table-name (columns not mandatory)
query

Now the following is the simple example to insert multiple values in the table using a single INSERT Query.

INSERT INTO [EmployeeDetail] ( emp_fname,emp_lname ,emp_no,emp_add)

VALUES ('Ram','Kumar', 12, 'Delhi'),

       ('Smith', 'Kumar',15,'Capetown'),

      ('Crown', 'sharma',100,'Sydney'),

      ('Copper', 'verma',39,'Jamaica'),

      ('lee', 'verma',45,'Sydney')

 

Now Press F5 to run the query and select query to see the result.

 

2. Insert multiple values in table using UNION ALL Query

The syntax for inserting multiple values is:

INSERT [EmployeeDetail](columns not mandatory)
SELECT  columnValue1,columnvalue2....columnValueN
UNION ALL
...........
...........

Now the following is the simple example to insert multiple values into the table using a UNION ALL Query:

INSERT [EmployeeDetail]( emp_fname,emp_lname ,emp_no,emp_add)

    SELECT  'Ram','Kumar', 12, 'Delhi'

    UNION ALL

    SELECT  'Smith', 'Kumar',15,'Capetown'

    UNION ALL

    SELECT 'Crown', 'sharma',100,'Sydney'

    UNION ALL

    SELECT 'Copper', 'verma',39,'Jamaica'

    UNION ALL

    SELECT 'lee', 'verma',45,'Sydney'

Now Press F5 to run the query and see the result, as in: