在一个SQL查询中多次插入

DB: SQL server

DB：SQL服务器

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';

插入table1选择'1'，'2'UNION ALL选择'3'，'4'UNION ALL选择'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..

是否还有其他机会在较少的时间内进行插入。它也作为Web请求执行。我猜这里的批量插入不适合，因为我没有文件中的数据来进行批量插入。有什么建议请..

4 个解决方案

#1

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

如果源数据库也是SQL Server，则可以添加链接服务器并：

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.

如果从.NET代码插入，插入多行的最快方法是SqlBulkCopy。 SqlBulkCopy确实需要DBO权限。

#2

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.

这实际上是我见过的最好的多重插入。注意SQL注入，始终在ASP.NET中使用CommandParameters或在MySQL中使用mysql_real_escape。

#3

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.

我最近调查了这篇文章，来自MySQL并希望从cularis的答案中获得语法，但经过一些搜索，我发现你能在答案中找到的语法。

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

编辑：看起来像cularis删除了他的答案，他正在谈论INSERT INTO x VALUES（1,2），（3,4）;句法。

#4

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

如果您使用的是SQL Server 2008和存储过程，则始终可以使用表值参数：

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

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

然后它变成INSERT INTO ... SELECT * FROM ...

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

这有助于防止注射问题。不确定参数化SQL是否可行。

#1