Caveman's Blog

My commitment to learning.

Sql Server: Insert multiple rows with one insert statement

with 2 comments


Typical style of scripting the insert of multple rows into a table is by writing multiple insert statments as follows:

declare @TempTable table(myData varchar(50))

insert into @TempTable(myData)
values('data 1')
insert into @TempTable(myData)
values('data 2')
insert into @TempTable(myData)
values('data 3')
insert into @TempTable(myData)
values('data 4')

Following are two ways of doing the same with less script code. The first style is supported uses comma seperated values:

declare @TempTable table(myData varchar(50))

insert into @TempTable(myData)
values('data 1'),
('data 2'),
('data 3'),
('data 4')

This style of scrpting uses UNION ALL:

declare @TempTable table(myData varchar(50))
insert into @TempTable(myData)
select 'data 1'
union all
select 'data 2'
union all
select 'data 3'
union all
select 'data 4'
Advertisements

Written by cavemansblog

July 11, 2011 at 11:51 am

2 Responses

Subscribe to comments with RSS.

  1. This is actually a very helpful post regarding the sql servers inserting of multiple rows, one question I did was regarding the union use all scripts….is there anyway to auto pick up all the data? Usually the data exceeds thousand +

    Alan Harper

    August 10, 2011 at 9:23 pm

  2. I actually tend to go along with pretty much everything that has
    been composed within “Sql Server: Insert multiple rows with one insert statement Caveman’s Blog”. Thank you for all of the actual info.I appreciate it,April


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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

%d bloggers like this: