SQL Server 2005:使用单个查询插入多行 [英] SQL Server 2005: Insert multiple rows with single query

问题描述

这应该是一个相当简单的问题，但是我还没有在线找到可靠的答案.我试图将多个行插入到同一表中，但只有一条语句.我在网上看到的最受欢迎的是以下内容，但我读到它仅适用于SQL Server 2008:

This should be a fairly straightforward question, but I haven't been able to find a solid answer online. I'm trying to insert multiple rows into the same table, but with only one statement. The most popular I've seen online is the following, but I've read that it only works with SQL Server 2008:

INSERT INTO Table (Name, Location) VALUES
('Name1', 'Location1'),
('Name2', 'Location2'),
('Name3', 'Location3'), etc...

如果此方法可用于SQL Server 2005，我更喜欢此方法，但我认为不会.根据我的阅读，另一个选项与INSERT之后的UNION ALL以下SELECT语句有关，这似乎很笨拙.有谁知道在2005年执行此操作的最佳语法?

I'd prefer this method if it will work with SQL Server 2005, but I don't think it will. The other option, from what I've read, has to do with UNION ALL's following SELECT statements after the INSERT, which seems clunky. Does anyone know for sure the best syntax to do this in 2005?

谢谢.

推荐答案

是的.您必须在SQL Server 2005中使用UNION ALL在单个语句中的SQL脚本中插入多行.

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' 

另一种主要选择是多次重复Insert语句，这更加冗长.在最后一种情况下，您需要小心使用显式事务，以避免许多单独提交的开销(当然，出于原子性原因)

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.

最后，如果这是您要脚本化的数据库中已有的数据(可能部署在另一台服务器上)，则 SSMS工具Pack 插件具有生成插入语句"功能，可以为您生成这些语句.

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.

这篇关于SQL Server 2005:使用单个查询插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！