Inserting Multiple Rows before SQL Server 2008

UNION ALL Trick 

 In the last published article I described how you can use the updated syntax of the INSERT statement in SQL Server 2008 to insert multiple rows of raw data using a single command. I mentioned that in order to achieve the same results in SQL Server 2005 or earlier editions you needed to use the "UNION ALL trick". Several readers asked me to explain this, so this is the topic of this article. As mentioned previously, SQL Server 2008 allows more than one row of data to be included in an INSERT statement, giving possibilities such as the following:

 INSERT INTO Salespeople (Name, Area, SalesTarget)
 VALUES ('Bob', 'East', 100000), 
 ('Jim', 'West', 120000), 
 ('Mel', 'North', 110000),
 ('Sue', 'Central', 120000)

The above command inserts four new rows into the Salespeople table. If you try to execute it in SQL Server 2005 or an earlier version, you will receive an error. It is possible to insert multiple rows at once in earlier editions but only if the information being added is produced using a query and inserted using the INSERT INTO SELECT version of the INSERT statement. If you want to insert multiple rows of raw data, you simply need to convert those data into a query. The best approach for this is to use a SELECT statement for each row and combine the results using UNION ALL. The revised version of the above sample script would be:

INSERT INTO Salespeople (Name, Area, SalesTarget)
 SELECT 'Bob', 'East', 100000 
UNION ALL SELECT 'Jim', 'West', 120000 
UNION ALL SELECT 'Mel', 'North', 110000 
UNION ALL SELECT 'Sue', 'Central', 120000

 NB: You can also use the UNION command without the ALL clause to combine rows. This will cause any duplicated rows to be inserted only once. Using UNION ALL preserves the duplicates.