Insert using Row Constructor

Row Constructor is new feature to SQL Server 2008 that allows insertion of multiple rows of data at once. Say we create a table row_constructor

create table row_construct

(ID int identity(1,1) not null primary key

, type varchar(20) not null default 'N/A'

, name varchar(100) not null default 'N/A'

)

 

insert row_construct (type,name) values ('A', 'Garments')

insert row_construct (type,name) values ('B', 'Sports Equipments')

insert row_construct (type,name) values ('C', 'Cosmetics')

insert row_construct (type,name) values ('A', 'Swim Wears')

insert row_construct (type,name) values ('A', 'Sports Garments')

 

--but with the help of row constructors we can insert the same data using single insert statement as below

 

insert row_construct (type,name) values

('A', 'Garments'),('B', 'Sports Equipments'),('C', 'Cosmetics'),('A', 'Swim Wears'),('A', 'Sports Garments');

--we can also write the same as

insert row_construct

select type, name from (values

('A', 'Garments'),('B', 'Sports Equipments'),('C', 'Cosmetics'),('A', 'Swim Wears'),('A', 'Sports Garments')) as rowtables (type, name);

 

--Limitations:

--We cannot select from other table in values clause as below it will only give "Incorrect syntax near the keyword 'select'."

 

insert row_construct

select type, name from

(

values

(select 'A', 'Garments'),('B', 'Sports Equipments'),('C', 'Cosmetics'),('A', 'Swim Wears'),('A', 'Sports Garments')

) as rowtables (type, name);

 

insert row_construct (type,name) values

(select 'A', 'Garments'),('B', 'Sports Equipments'),('C', 'Cosmetics'),('A', 'Swim Wears'),('A', 'Sports Garments');

 

--We can insert from multiple tables only by using UNION/UNION ALL clause as below

 

insert row_construct

select 'A', 'Garments'

union

select 'B', 'Sports Equipments'

union

select 'C', 'Cosmetics'

Tags: , , ,

SQL Server | SQL Tricks