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

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...