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