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
go
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
