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?!