一个 SQL 查询中的多次插入
DB：SQL Server
我正在使用以下构造将多条记录插入到表中。我正在从其他数据库接收数据（要插入）。
插入表1 选择 '1','2' UNION ALL 选择 '3','4' UNION ALL 选择“5”、“6”；
还有其他机会在更短的周转时间内进行插入吗？它也作为网络请求执行。我想批量插入不适合这里，因为我的文件中没有数据来进行批量插入。 有什么建议请..
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..
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
如果源数据库也是 SQL Server，则可以添加链接服务器并且：
如果从 .NET 代码插入，插入多行的最快方法是 SqlBulkCopy。 SqlBulkCopy 确实需要 DBO 权限。
If the source database is also a SQL Server, you could add a linked server and:
If you're inserting from .NET code, the fastest way to insert multiple rows is SqlBulkCopy. SqlBulkCopy does require DBO rights.
这实际上是我见过的最好的多重插入。只是要小心 SQL 注入，始终在 ASP.NET 中使用 CommandParameters 或在 MySQL 中使用 mysql_real_escape。
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.
我最近对此进行了研究，来自 MySQL，并期待 ularis 答案中的语法，但经过一番搜索后，我所能找到的只是您在答案中发布的语法。
编辑：看起来 ularis 删除了他的答案，他正在谈论
INSERT INTO x VALUES (1, 2), (3, 4);语法。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.如果您使用 SQL Server 2008 和存储过程，则始终可以使用表值参数：
http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters
然后它变成
INSERT INTO ... SELECT * FROM ...这将有助于解决注入问题。不确定这是否可以通过参数化 SQL 实现。
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.