将多行默认值插入到表中。

I have a table with a single column, which is an auto-generated identity

我有一个只有一列的表，这是一个自动生成的标识

create table SingleIdTable (
   id int identity(1,1) not null
)

I can insert a single row with an auto-generated id with:

我可以用自动生成的id插入一行:

insert into SingleIdTable default values

I want to insert many rows and use the output syntax to get their ids, something like:

我想插入许多行并用输出语法获取它们的id，比如:

insert into SingleIdTable
output inserted.Id into @TableOfIds
    select (default values) from SomeOtherTable where Attribute is null

Where the intention is to insert a row into SingleIdTable for each row in SomeOtherTable where Attribute is null using an auto-generated id. The above doesn't work, but how could I do it. I note that if my table had more than just a single column I could do it, but I can't select empty rows which is what I really want to do.

其中的意图是将一个行插入到另一个表中的每一行，而在另一个表中，使用自动生成的id，属性为null。我注意到，如果我的表不止有一个列，我可以这么做，但我不能选择空行，这正是我真正想做的。

I can't change the definition of SomeOtherTable.

我不能改变另一个表的定义。

2 个解决方案

#1

If SQL Server 2008+ you can use MERGE for this. Example syntax below.

如果SQL Server 2008+，您可以使用MERGE来实现这一点。下面的示例语法。

MERGE INTO SingleIdTable
USING (SELECT *
       FROM   SomeOtherTable
       WHERE  Attribute IS NULL) T
ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT
  DEFAULT VALUES
OUTPUT INSERTED.id;

I'm not sure what practical use this single column table has though?

我不确定这个单列表有什么实际用途?

#2

you did not specify which version of SQL Server you are on. If you happen to be on SQL 2012 you probably can replace you SingleIdTable with a sequence: http://msdn.microsoft.com/en-us/library/ff878091.aspx

您没有指定您使用的是哪个版本的SQL Server。如果您碰巧在SQL 2012上，您可能可以用一个序列替换单例表:http://msdn.microsoft.com/en-us/library/ff878091.aspx

#1