SQL Server with Mr. Denny

Apr 3 2008   11:00AM GMT

New INSERT syntax in SQL Server 2008



Posted by: mrdenny
T/SQL, SQL Server 2008, INSERT

One of the very cool new feature which SQL Server 2008 gives us is an change to the INSERT statement.  Now you can specify multiple rows to insert into a table from a single insert command.

The syntax is:
CREATE TABLE TableName (Column1 INT, Column2 VARCHAR(10))
INSERT INTO TableName
(Column1, Column2)
VALUES
(1, ‘test1′), (2, ‘test2′), (3, ‘test4′)

I see this as being a very handy especially when doing an initial data load into a table as you can now load lots of data without having to run a lot of seperate insert statements.

Denny

Comment on this Post


You must be logged-in to post a comment. Log-in/Register

Jan.novak  |   Apr 22 2008   7:09AM GMT

Hi Denny and all.

It is good to hear, that this option is also available in SQL server 2008.

I am not expert, but I think this syntax will cause overhead if you insert many records. Maybe is better choice:
INSERT INTO …
(col1, col2,…)
SELECT …
FROM …
UNION ALL
SELECT …
FROM …