CODEWITHSUNDEEP
sqlsql-serverinsertbulkinsert
Muthukumar Palaniappan
Muthukumar Palaniappan

Reputation: 1670

Multiple inserts in one SQL query

DB: SQL server

I am using the below construct for inserting multiple records into a table. I am receiving the data (to be inserted) from other DB.

Insert into table1 select '1','2' UNION ALL select '3','4' UNION ALL select '5','6';

would there be any other chance in doing inserts in less turn around time. Its also been executed as a web request. I guess bulk insert would not fit here, as I don't have the data in a file to do a bulk insert. Any suggestions please..

Upvotes: 4

Views: 1507

Answers (4)

Adam Houldsworth
Adam Houldsworth

Reputation: 64467

If you are using SQL Server 2008 and stored procedures, you could always make use of table valued parameters:

http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters

It then becomes an INSERT INTO ... SELECT * FROM ...

This would help against injection problems. Not sure if this is possible with parameterised SQL.

Upvotes: 0

Aistina
Aistina

Reputation: 12683

I looked into this recently, coming from MySQL and expecting the syntax from cularis' answer, but after some searching all I could find is the syntax you posted in your answer.

Edit: Looks like cularis removed his answer, he was talking about the INSERT INTO x VALUES (1, 2), (3, 4); syntax.

Upvotes: 0

Andomar
Andomar

Reputation: 238048

If the source database is also a SQL Server, you could add a linked server and:

insert table1 select * from linkedserver.dbname.dbo.table1

If you're inserting from .NET code, the fastest way to insert multiple rows is SqlBulkCopy. SqlBulkCopy does require DBO rights.

Upvotes: 1

kazinix
kazinix

Reputation: 30093

That is actually the best multiple insert I have ever seen. Just be careful to SQL injections, always use CommandParameters in ASP.NET or use mysql_real_escape in MySQL.

Upvotes: 0

Related Questions