How can you insert multiple rows together in a single insert statement for a table?

 Posted by Virendradugar on 12/3/2009 | Category: Sql Server Interview questions | Views: 5896
Answer:

It is not possible to insert multiple rows with a single insert statement. If you want to insert multiple rows, one have to write multiple insert statements considering that you are working SQL server 2005 or it's below version.

But SQL Server 2008 allows to insert multiple rows with a single insert statement.

As for example,

In sql server 2005, if we need to insert multiple row, we used to do something like this.

CREATE TABLE [State] (

[StateID] int,
[StateName] VARCHAR(20)
)
GO

INSERT INTO State
VALUES (1, 'Gujarat')

INSERT INTO State
VALUES (2, 'Dehli')

INSERT INTO State
VALUES (3, 'Bihar')



But with SQL Server 2008 we can combine all the three insert statement in single insert statement. See below:

CREATE TABLE [State] (

[StateID] int,
[StateName] VARCHAR(20)
)
GO

INSERT INTO State
VALUES (1, 'Gujarat'),
(2, 'Dehli'),
(3, 'Bihar')


Hope this helps...


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: Poster on: 12/3/2009
Thanks Virendra, can you give any example how to write multiple rows in SQL Server 2008?

Regards,
Posted by: Lakhangarg on: 12/3/2009
Hi Virendradugar -

it will be better if you can write any example of your above question. because we can insert multiple rows in using single insert statement using select statement.

Thanks & Regards
Lakhan Pal Garg
Posted by: Virendradugar on: 12/3/2009
Hi Poster and Lakhan Pal,

I have updated the answer with an example.

Hope this helps..

Thanks,
Virendra Dugar

Login to post response