SQL Server 2005：使用单个查询插入多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3272487/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2005: Insert multiple rows with single query
提问by MegaMatt
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:
这应该是一个相当简单的问题，但我无法在网上找到可靠的答案。我试图在同一个表中插入多行，但只有一个语句。我在网上看到的最流行的是以下内容，但我读到它仅适用于 SQL Server 2008：
INSERT INTO Table (Name, Location) VALUES
('Name1', 'Location1'),
('Name2', 'Location2'),
('Name3', 'Location3'), etc...
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 在 INSERT 之后的以下 SELECT 语句有关，这看起来很笨拙。有没有人确定在 2005 年执行此操作的最佳语法？
Thanks.
谢谢。
回答by Martin Smith
Yep. You have to use UNION ALLs in SQL Server 2005 to insert multiple rows in a SQL script in a single statement.
是的。您必须UNION ALL在 SQL Server 2005 中使用s 在单个语句中的 SQL 脚本中插入多行。
INSERT INTO Table
(Name, Location)
SELECT 'Name1', 'Location1'
UNION ALL
SELECT 'Name2', 'Location2'
UNION ALL
SELECT 'Name3', 'Location3'
The other main alternative is to repeat the Insertstatement 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)
另一个主要的替代方法是Insert多次重复该语句，这更加冗长。在最后一种情况下，您需要小心使用显式事务以避免许多单独提交的开销（当然也是出于原子性原因）
If you have lots of rows to insert you could use BULK INSERTto load it all in from a delimited file in one statement.
如果您有很多行要插入，您可以使用BULK INSERT在一个语句中从分隔文件中将其全部加载。
Finally if this is data already in the database that you are scripting out (perhaps to deploy on another server) the SSMS Tools Packaddin has a "Generate Insert Statements" function that can generate these statements for you.
最后，如果这是您正在编写脚本的数据库中已有的数据（可能部署在另一台服务器上），SSMS 工具包插件具有“生成插入语句”功能，可以为您生成这些语句。
回答by onedaywhen
As others have said, the key here is UNION ALL. For me, using a CTE keeps things looking a little cleaner e.g.
正如其他人所说，这里的关键是UNION ALL. 对我来说，使用 CTE 让事情看起来更干净，例如
WITH NewStuff (Name, Location)
AS
(
SELECT 'Name1', 'Location1' UNION ALL
SELECT 'Name2', 'Location2' UNION ALL
SELECT 'Name3', 'Location3'
)
INSERT INTO Stuff (Name, Location)
SELECT Name, Location
FROM NewStuff;
回答by Donnie
You have to use the union allin sql server 2005. To be honest, that's so clunky and ugly, I'd just use multiple insertsif I were you. Wrap them in a single transaction and it's the same thing in the end.
你必须union all在 sql server 2005 中使用。老实说，这太笨重和丑陋了，inserts如果我是你，我只会使用多个。将它们包装在一个事务中，最终结果是一样的。
回答by Chris Diver
Yes they are your only options, unless you are inserting a lot of data and might want to explore a BULK INSERT
是的，它们是您唯一的选择，除非您要插入大量数据并且可能想要探索 BULK INSERT
INSERT INTO Table (Name, Location)
SELECT 'Name1', 'Location1' UNION ALL
SELECT 'Name2', 'Location2' UNION ALL
SELECT 'Name3', 'Location3'
回答by Appyks
Since MS SQLServer 2005 supports XML the best method I would suggest is a STORED PROCEDURE with an input parameter of XML type. If you are working with .NET you can easily convert the DataSet to xml string using ds.GetXml() method and can be sent to the SP
由于 MS SQLServer 2005 支持 XML，我建议的最佳方法是使用 XML 类型的输入参数的 STORED PROCEDURE。如果您使用 .NET，您可以使用 ds.GetXml() 方法轻松地将 DataSet 转换为 xml 字符串，并且可以发送到 SP
CREATE PROCEDURE [dbo].[insertLocation](@XML XML=NULL)
AS
BEGIN
INSERT INTO [dbo].[TheLocations]
( [Name], [Location] )
SELECT
XTab.value('Name[1]','nvarchar(100)') AS[Name],
XTab.value('Location[1]','nvarchar(200)') AS[Location]
FROM @XML.nodes('TheLocations') XTab([XTab])
END
