Prior to SQL Server 2008 it is not possible to provide multiple sets of raw data to an INSERT statement to create more than one row in a table. However, it is possible to insert a query’s results into a table. This can be used to add several rows at once.
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:
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:
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.