Web developers wiki ASP.NET Sitecore Sharepoint Kentico by Evident Interactive

Multiple inserts in SQL Server 2008

Modified: 2008/07/21 10:03 by vanthoog - Categorized as: SQL Server
Suppose you want to insert a number of records into a table. In SQL Server 2005 you will need to use a number of separate INSERT statements. But in SQL Server 2008 you can achieve this by using a single INSERT statement.

The following example uses one INSERT statement for inserting 5 records into a table variable:

declare @tbl table (ID int, jobtitle nvarchar(32));

insert into @tbl (ID, jobtitle)
values
	(1 , 'Secretary'),
	(2 , 'Managing director'),
	(3 , 'Security officer'),
	(4 , 'Software developer'),
	(5 , 'Sales manager')

select * from @tbl

(5 row(s) affected)
ID          jobtitle
----------- --------------------------------
1           Secretary
2           Managing director
3           Security officer
4           Software developer
5           Sales manager

(5 row(s) affected)

The entire INSERT statement is treated as a single transaction. Therefore, if one insert fails, the entire transaction is rolled back and no records are inserted. In the following example the sixth insert will fail because the jobtitle is too long, and the result is that after the INSERT statement the table is still empty:

declare @tbl table (ID int, jobtitle nvarchar(32));

insert into @tbl (ID, jobtitle)
values
	(1 , 'Secretary'),
	(2 , 'Managing director'),
	(3 , 'Security officer'),
	(4 , 'Software developer'),
	(5 , 'Sales manager'),
	(6 , 'Interim manager of the test department'),
	(7 , 'System manager')

select * from @tbl

Msg 8152, Level 16, State 4, Line 3
String or binary data would be truncated.
The statement has been terminated.

ID          jobtitle
----------- --------------------------------

(0 row(s) affected)

 © Evident Interactive BV