Bulk Insert in SQL Server using Union All

 Recently I got an interesting question from one of new developer.

i.e. How can I insert multiple rows in one insert ? This is intresting and most of us came across this situation.

Most of the common way we follow is :

GO

  INSERT INTO Table (Column1, Column2)     

                VALUES ('First',1);

INSERT INTO Table  (Column1, Column2)    

               VALUES ('Second',2);

INSERT INTO Table  (Column1, Column2)    

               VALUES ('Third',3);

INSERT INTO Table (Column1, Column2)     

               VALUES ('Fourth',4);

INSERT INTO Table (Column1, Column2)     

               VALUES ('Fifth',5);

GO

We have three options :-

·         INSERT INTO

·         UNION ALL

·         and INSERT INTO … SELECT… clauses

Regarding the performance we have lot of difference.

I prefer using UNION ALL and explained the same to him and he was impressed.

Below is the SYNTAX

USE YourDB
GOINSERT INTO Table (Column1, Column2)

SELECT 'First' ,1      UNION ALLSELECT 'Second' ,2 UNION ALLSELECT 'Third' ,3    UNION ALLSELECT 'Fourth' ,4  UNION ALLSELECT 'Fifth' ,5

GO

The other main alternative is to repeat the Insert statement multiple times which is even more verbose. You need to be careful to use Explicit transactions in this last case to avoid the overhead of many individual commits (and for atomicity reasons of course)

If you have lots of rows to insert you could use BULK INSERT to load it all in from a delimited file in one statement.