SQL Server 2005:用单个查询插入多行。

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.

谢谢。

5 个解决方案

#1

Yep. You have to use UNION ALLs in SQL Server 2005 to insert multiple rows in a SQL script in a single statement.

是的。您必须在SQL Server 2005中使用UNION ALLs在一个语句中在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 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.

如果有很多行要插入，可以使用批量插入从带分隔符的文件中加载到一条语句中。

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.

最后，如果这是您正在编写的数据库中的数据(可能在另一个服务器上部署)，那么SSMS工具包中的addin有一个“Generate Insert语句”函数，它可以为您生成这些语句。

#2

As others have said, the key here is UNION ALL. For me, using a CTE keeps things looking a little cleaner e.g.

正如其他人所说，这里的关键是团结一致。对我来说，使用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;

#3

You have to use the union all in sql server 2005. To be honest, that's so clunky and ugly, I'd just use multiple inserts if I were you. Wrap them in a single transaction and it's the same thing in the end.

您必须在sql server 2005中使用所有的联合。老实说，那是如此的笨拙和丑陋，如果我是你的话，我会用多重插入。将它们打包到一个事务中，最后也是一样的。

#4

Yes they are your only options, unless you are inserting a lot of data and might want to explore a BULK INSERT

是的，它们是您唯一的选择，除非您正在插入大量数据，并且可能想要探索批量插入

INSERT INTO Table (Name, Location)
SELECT 'Name1', 'Location1' UNION ALL
SELECT 'Name2', 'Location2' UNION ALL
SELECT 'Name3', 'Location3'

#5

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类型的输入参数的存储过程。如果您正在使用。net，您可以使用ds.GetXml()方法轻松地将数据集转换为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

#1