New INSERT syntax in SQL Server 2008

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

Share

One Response

  1. 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 …

Leave a Reply to Jan.novakCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?