Insert Multiple Values Using Insert into (SQL Server 2005)

SQL Server 2005: Insert multiple rows with single query

Yep. You have to use UNION ALLs in SQL Server 2005 to insert multiple rows in a SQL script in a single statement.

INSERT INTO Table 
  (Name, Location) 
SELECT 'Name1', 'Location1' 
UNION ALL
SELECT 'Name2', 'Location2'
UNION ALL
SELECT 'Name3', 'Location3' 

The other main alternative is to repeat the Insert statement multiple times which is even more verbose. You need to be careful to use Explicit transactions in this last case to avoid the overhead of many individual commits (and for atomicity reasons of course)

If you have lots of rows to insert you could use BULK INSERT to load it all in from a delimited file in one statement.

Finally if this is data already in the database that you are scripting out (perhaps to deploy on another server) the SSMS Tools Pack addin has a "Generate Insert Statements" function that can generate these statements for you.

Insert multiple values using INSERT INTO (SQL Server 2005)

The syntax you are using is new to SQL Server 2008:

INSERT INTO [MyDB].[dbo].[MyTable]
       ([FieldID]
       ,[Description])
 VALUES
       (1000,N'test'),(1001,N'test2')

For SQL Server 2005, you will have to use multiple INSERT statements:

INSERT INTO [MyDB].[dbo].[MyTable]
       ([FieldID]
       ,[Description])
 VALUES
       (1000,N'test')

INSERT INTO [MyDB].[dbo].[MyTable]
       ([FieldID]
       ,[Description])
 VALUES
       (1001,N'test2')

One other option is to use UNION ALL:

INSERT INTO [MyDB].[dbo].[MyTable]
       ([FieldID]
       ,[Description])
SELECT 1000, N'test' UNION ALL
SELECT 1001, N'test2'

Insert multiple rows WITHOUT repeating the INSERT INTO ... part of the statement?

INSERT INTO dbo.MyTable (ID, Name)
SELECT 123, 'Timmy'
UNION ALL
SELECT 124, 'Jonny'
UNION ALL
SELECT 125, 'Sally'

For SQL Server 2008, can do it in one VALUES clause exactly as per the statement in your question (you just need to add a comma to separate each values statement)...

insert multiple rows in single SQL

you have to remove UNION ALL before the first SELECT.

INSERT INTO login (LogInID,Password)
SELECT 'Name1','pass1'
UNION ALL
SELECT 'Name2','pass2'

inserting multiple values for an entity at once

I agree with Marc - do it in a transaction, but if you really want a single statement, then you could do:

insert into paid (username, prd_name, paid_amt)
select 'jone', 'prd1', 10
union all
select 'jone', 'prd2', 20
union all
select 'jone', 'prd3', 30

Insert multiple rows into SQL Server 2005 using a stored procedure

Do you use ADO or Entity?

You can use transactions from your code.
For ADO

try
{
    sqlTransaction = sqlConnection.BeginTransaction();

    //call insert in loop

    sqlTransaction.Commit();
}
catch{
    sqlTransaction.Rollback();
}

For Entity

try
{
    entityTransaction = Context.Connection.BeginTransaction();

    //call insert in loop

    entityTransaction.Commit();
}
catch{
    entityTransaction.Rollback();
}

Insert statement in SQL Server 2005

SQL Server 2005 does not support that insert syntax; you would either need

insert into IDName 
SELECT 101 , 'AA'
UNION ALL SELECT 301 , 'BB'

or

insert into IDName VALUES (101 , 'AA');
insert into IDName VALUES (301 , 'BB');

---

Related Topics