将多行默认值插入表中 [英] Insert multiple rows of default values into a table
问题描述
我有一个单列的表,这是一个自动生成的身份
I have a table with a single column, which is an auto-generated identity
create table SingleIdTable (
id int identity(1,1) not null
)
我可以插入带有自动生成的 ID 的单行:
I can insert a single row with an auto-generated id with:
insert into SingleIdTable default values
我想插入多行并使用输出语法来获取它们的 id,例如:
I want to insert many rows and use the output syntax to get their ids, something like:
insert into SingleIdTable
output inserted.Id into @TableOfIds
select (default values) from SomeOtherTable where Attribute is null
其目的是为 SomeOtherTable
中的每一行在 SingleIdTable
中插入一行,其中 Attribute
使用自动生成的 ID 为空.以上不起作用,但我怎么做.我注意到,如果我的表格不止一列,我可以做到,但我不能选择空行,而这正是我真正想要做的.
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.
我无法更改SomeOtherTable
的定义.
推荐答案
如果 SQL Server 2008+ 你可以使用 MERGE
来解决这个问题.下面的示例语法.
If SQL Server 2008+ you can use MERGE
for this. Example syntax below.
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?
这篇关于将多行默认值插入表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!