RSS

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, ... '']) 
VALUES 
(''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'
UNION ALL
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?!

Advertisements
 
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
%d bloggers like this: