如何插入多条记录并获取标识值? [英] How to insert multiple records and get the identity value?

问题描述

我正在从另一个表 B 向表 A 中插入多条记录.有没有办法在不做游标的情况下获取表 A 记录的标识值并更新表 b 记录?

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?

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

我使用的是 MS SQL Server 2005.

I'm using MS SQL Server 2005.

推荐答案

MBelly 在金钱上是正确的 - 但是触发器将始终尝试更新表 B，即使这不是必需的(因为您也从表 C 插入?).

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?).

Darren 在这里也是正确的，您无法将多个身份作为结果集取回.您的选择是使用游标并为您插入的每一行获取标识，或者使用 Darren 的前后存储标识的方法.只要您知道身份的增量，这应该可以工作，只要您确保所有三个事件的表都被锁定.

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.

如果是我，而且时间紧迫，我会选择使用光标.

If it was me, and it wasn't time critical I'd go with a cursor.

这篇关于如何插入多条记录并获取标识值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！