Systems Engineering and RDBMS

Simulating Row Value Constructor in versions prior to SQL Server 2008

Posted by decipherinfosys on December 26, 2008

This was another question from one of our readers: “This is in reference to your post on the new feature of row value constructor in SQL Server 2008. Is there a way to do this in SQL Server 2000 or SQL Server 2005?”.

The answer is Yes – you can do it by using: “Insert into…. SELECT …” statement.  Here is an example:

use deciphertest
create table dbo.decipher_test (col1 int, col2 int);

/*Normal way of inserting the data*/
insert into dbo.decipher_test values (1, 10);
insert into dbo.decipher_test values (2, 20);
insert into dbo.decipher_test values (3, 30);

/*Using row value constructor in SQL Server 2008*/
Insert into dbo.decipher_test (col1, col2) values (1, 10), (2, 20), (3, 30)

/*Another method which will work regardless of the version*/
insert into dbo.decipher_test (col1, col2)
select 1, 10
union all
select 2, 20
union all
select 3, 30

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 )

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


Get every new post delivered to your Inbox.

Join 92 other followers

%d bloggers like this: