SQL 如何插入多条记录并获取标识值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/95988/
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
How to insert multiple records and get the identity value?
提问by Dwight T
I'm inserting multiple records into a table A from another table B. Is there a way to get the identity value of table A record and update table b record with out doing a cursor?
我正在从另一个表 B 向表 A 中插入多条记录。有没有办法在不使用游标的情况下获取表 A 记录的标识值并更新表 b 记录？
Create Table A
(id int identity,
Fname nvarchar(50),
Lname nvarchar(50))
Create Table B
(Fname nvarchar(50),
Lname nvarchar(50),
NewId int)
Insert into A(fname, lname)
SELECT fname, lname
FROM B
I'm using MS SQL Server 2005.
我正在使用 MS SQL Server 2005。
采纳答案by Meff
MBelly is right on the money - But then the trigger will always try and update table B even if that's not required (Because you're also inserting from table C?).
MBelly 在金钱上是正确的 - 但是即使不需要，触发器也会始终尝试更新表 B（因为您也是从表 C 插入的？）。
Darren is also correct here, you can't get multiple identities back as a result set. Your options are using a cursor and taking the identity for each row you insert, or using Darren's approach of storing the identity before and after. So long as you know the increment of the identity this should work, so long as you make sure the table is locked for all three events.
Darren 在这里也是正确的，您无法将多个身份作为结果集取回。您的选择是使用游标并为您插入的每一行获取标识，或者使用 Darren 的前后存储标识的方法。只要您知道身份的增量，这应该可以工作，只要您确保所有三个事件的表都被锁定。
If it was me, and it wasn't time critical I'd go with a cursor.
如果是我，而且时间不紧迫，我会使用光标。
回答by Andy Irving
Use the ouput clause from 2005:
使用 2005 年的输出子句：
DECLARE @output TABLE (id int)
Insert into A (fname, lname)
OUTPUT inserted.ID INTO @output
SELECT fname, lname FROM B
select * from @output
now your table variable has the identity values of all the rows you insert.
现在您的表变量具有您插入的所有行的标识值。
回答by njr101
Reading your question carefully, you just want to update table B based on the new identity values in table A.
仔细阅读您的问题，您只想根据表 A 中的新标识值更新表 B。
After the insert is finished, just run an update...
插入完成后，只需运行更新...
UPDATE B
SET NewID = A.ID
FROM B INNER JOIN A
ON (B.FName = A.Fname AND B.LName = A.LName)
This assumes that the FName / LName combination can be used to key match the records between the tables. If this is not the case, you may need to add extra fields to ensure the records match correctly.
这假定 FName / LName 组合可用于键匹配表之间的记录。如果不是这种情况，您可能需要添加额外的字段以确保记录正确匹配。
If you don't have an alternate key that allows you to match the records then it doesn't make sense at all, since the records in table B can't be distinguished from one another.
如果您没有允许您匹配记录的备用键，那么它根本没有意义，因为表 B 中的记录无法相互区分。
回答by Cory
As far as I understand it the issue you are having is that you want to INSERT into Table A, which has an identity column, and you want to preserve the identity from Table B which does not.
据我了解，您遇到的问题是您想插入具有标识列的表 A，并且您想保留表 B 中没有的标识。
In order to do that you should just have to turn on identity insert on table A. This will allow you to define your ID's on insert and as long as they don't conflict, you should be fine. Then you can just do:
为了做到这一点，您只需要在表 A 上打开身份插入。这将允许您在插入时定义您的 ID，只要它们不冲突，您应该没问题。然后你可以这样做：
Insert into A(identity, fname, lname) SELECT newid, fname, lname FROM B
Not sure what DB you are using but for sql server the command to turn on identity insert would be:
不确定您使用的是什么数据库，但对于 sql server，打开身份插入的命令是：
set identity_insert A on
回答by Dmitry Khalatov
I suggest using uniqueidentifier type instead of identity. I this case you can generate IDs before insertion:
我建议使用 uniqueidentifier 类型而不是身份。在这种情况下，您可以在插入前生成 ID：
update B set NewID = NEWID()
insert into A(fname,lname,id) select fname,lname,NewID from B
回答by Matt
If you always want this behavior, you could put an AFTER INSERT trigger on TableA that will update table B.
如果你总是想要这种行为，你可以在 TableA 上放置一个 AFTER INSERT 触发器来更新表 B。
回答by Darren Kopp
You can get the by joining on the row number. This is possible because since it's an identity, it will just increment as you add items, which will be in the order that you are selecting them.
您可以通过加入行号来获得。这是可能的，因为它是一个身份，它只会随着您添加项目而增加，这将按照您选择它们的顺序。
