Tuesday, 30 April 2013

Insert multiple rows in one statement in SQL Server 2008

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);


Or

insert row_construct
values

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



See the Results...
Enjoy....
More  Info:  http://blog.sqlauthority.com/2008/04/16/sql-server-2008-row-constructors-load-temp-tables-from-stored-procedures/ 

No comments:

Post a Comment