inserting multiple rows of data in sql server 2005 question

Question

inserting multiple rows of data in sql server 2005 question

kllrbeas

SSC Rookie

Points: 28
More actions
February 20, 2009 at 11:23 am

#128529

I am in my first sql class, I am trying to find out if sql server 2005 supports this syntax
Insert Into dbo.employees (ID, FirstName, LastName, Age)
Values('1018','Neeraj', 'Shivasam',18)
Values('1118','Neeraj', 'Huda',38)
Values('1028','Gaurav', 'Malhotra',30)
Values('1128','Abhishek', 'Prasad',30)
Values('2128','Pankaj', 'Nautiyal',36)
Values('3128','Ritesh', 'Kashyap',33)
I know 2008 does, we have been trying to determine if 2005 does or if there is a bug in our school system. Any documentation references would be greatly appreciated
Thnx in advance
Di

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29255 More actions · Answer 1

kllrbeas (2/20/2009)
I am in my first sql class, I am trying to find out if sql server 2005 supports this syntax
Insert Into dbo.employees (ID, FirstName, LastName, Age)
Values('1018','Neeraj', 'Shivasam',18)
Values('1118','Neeraj', 'Huda',38)
Values('1028','Gaurav', 'Malhotra',30)
Values('1128','Abhishek', 'Prasad',30)
Values('2128','Pankaj', 'Nautiyal',36)
Values('3128','Ritesh', 'Kashyap',33)
I know 2008 does, we have been trying to determine if 2005 does or if there is a bug in our school system. Any documentation references would be greatly appreciated
Thnx in advance
Di

You cannot have multiple VALUES clauses with one INSERT statement.

you can however achieve the result by writing this.

Insert Into dbo.employees (ID, FirstName, LastName, Age)

select '1018','Neeraj', 'Shivasam',18 UNION ALL

select '1118','Neeraj', 'Huda',38 UNION ALL

select '1028','Gaurav', 'Malhotra',30 UNION ALL

select '1128','Abhishek', 'Prasad',30 UNION ALL

select '2128','Pankaj', 'Nautiyal',36 UNION ALL

select '3128','Ritesh', 'Kashyap',33

Pradeep Singh

tcnolan SSCrazy Points: 2097 More actions · Answer 2

Unfortunately that is not valid syntax in sql 2005, but you could do something like the following...

insert into dbo.employees (ID, FirstName, LastName, Age)

select '1018','Neeraj', 'Shivasam',18

union all

select '1118','Neeraj', 'Huda',38

union all

select '1028','Gaurav', 'Malhotra',30

union all

select '1128','Abhishek', 'Prasad',30

union all

select '2128','Pankaj', 'Nautiyal',36

union all

select '3128','Ritesh', 'Kashyap',33

[edit]Sorry, for double post, I was beaten to the punch.[/edit]

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 3

Out of curiosity, where did that syntax come from (original post)? another platform or SQL version?

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

tcnolan SSCrazy Points: 2097 More actions · Answer 4

Actually good question because that syntax is not actually valid in sql 2008. you would need to use the following for sql 2008...

Insert Into dbo.employees (ID, FirstName, LastName, Age)

Values('1018','Neeraj', 'Shivasam',18),

('1118','Neeraj', 'Huda',38),

('1028','Gaurav', 'Malhotra',30),

('1128','Abhishek', 'Prasad',30),

('2128','Pankaj', 'Nautiyal',36),

('3128','Ritesh', 'Kashyap',33)

kllrbeas SSC Rookie Points: 28 More actions · Answer 5

Thanks for the response, the syntax that i posted is just an example, my professor and I were debating about if this could be done in sql server 2005, I was telling him that the select/union all was the only method to add multiple rows , he asked me to prove it. I was pretty sure I was right I was hoping to find it in writing somewhere. But thanks for your help.