Insert Multiple records in one statement

26 May

SQL-92 Standard added a new feature – row value constructors. It makes it possible to insert multiple rows at the same time in a single statement. Before SQL Server 2008 we had to either copy/paste a huge number of single INSERT statements or use SELECT/UNION clause in INSERT statement. Now, SQL Server 2008 supports this feature too and provides a new method to insert data to SQL Server tables.

General syntax of row value constructors is the following:

INSERT INTO ''TABLE'' (''column1'', [''column2, ... '']) 
(''value1a'', [''value1b, ...'']), 
(''value2a'', [''value2b, ...'']), 

To perform this operation on earlier versions of MS SQL Server that didn’t support row value constructors we used to use SELECT and UNION ALL clause:

INSERT INTO phone_book
SELECT 'John Doe', '555-1212'
SELECT 'Peter Doe', '555-2323'

This construction still works perfectly well and can make the code much shorter and look much better.

At last have a look at new SQL Server 2008 method of row construction:

INSERT INTO phone_book
VALUES ('John Doe','555-1212'),('Peter Doe','555-2323')

This feature is supported by DB2, SQL Server (since version 10.0), PostgreSQL (since version 8.2), MySQL, and H2.

Isn’t it cool?!

Leave a comment

Posted by on May 26, 2009 in SQL


Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: