6/30/2005

Batch Inserts in SQL Server

Filed under: General — russell @ 8:32 pm

Ever had to insert alot of data into SQL server and did not want to write each record, one at a time? Here's a tip:

Code: 
INSERT INTO [TABLE] (column1,column2)
select ('Value1','Value1A')
UNION ALL
select ('Value2','Value2A')
UNION ALL
select ('Value3','Value3A')

Note: UNION ALL versus UNION -- UNION ALL does NOT perform a distinct. UNION without ALL eliminates duplicate records (in the resulting recordset) in SQL Server.

Also the maximum batch (query) length in SQL server is (65,536 * 4K) characters long. It would be prudent to avoid passing that limit.