Multiple Value Inserts Using a Single INSERT Statement

With the introduction of row constructor in SQL 2008, you can now insert multiple values using a single INSERT statement.

With the row constructor, you can now insert multiple values using a single INSERT statement. In SQL Server 2000 or SQL Server 2005, to insert multiple rows to a table, you would have need of one INSERT statement for each record.   To exemplify, to insert the multiple records to EmpTest Table, you would do something like the following

insert into EmpTest values('sakshi',2)

insert into EmpTest values('Aishwarya',3)

insert into EmpTest values('Arti',2)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

The individual insert into... values statement can now be cut down with the row constructor.  The script above will now look as follows:

insert into EmpTest values('Vibha',1),

                        ('Sandhya',3),

                        ('Aurpita',1)

(3 row(s) affected)

SELECT with VALUES Clauses in the INSERT Statement Row Constructor

It allow a combination of specifying a list of values using the VALUES clause and getting data from another table using the SELECT statement

Note: Subqueries are allowed as long as the subquery returns only a single column and a single row

insert into EmpTest values('Paavai',2),

                          ('Edwina',3),

((select o.name from OneRowTable o where o.id =1),

                          (select '3'))

select * from EmpTest

emp_id      emp_name   dept_id

----------- ---------- -----------

1           sakshi     2

2           Aishwarya  3

3           Arti       2

4           Vibha      1

5           Sandhya    3

6           Aurpita    1

7           Paavai     2

8           Edwina     3

9           vikram     3

(9 row(s) affected)

Enclose grand code

Post Reference: Vikram Aristocratic Elfin Share