如何插入多条记录并获取标识值？

Question

我正在从另一个表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

我正在使用 MS SQL Server 2005。

- footndale

---

Andy Irving的回答是最好的。触发器笨拙，对于目标表上的任意操作效果不佳，特别是如果您的目标是临时的或仅是中间表。如果您要插入一组行，则它们在目标表中的顺序未必与您的集合顺序相同，因此Darren的回答是错误的。Dmitry的方法很糟糕，因为它需要循环逐个插入单行，这会影响性能，所以尽可能使用集合。Cory的方法也很糟糕，他解释说，“只要它们不冲突。”这将变成一个周六晚上的问题。 - clemahieu

我意识到这是一个旧问题，并且它指定了SQL Server 2005，但由于它是首个显示出来的结果，因此应该提及在2008及以后可用的MERGE语句，以供那些正在寻找解决方案的人参考。合并到 目标表 使用 ( SELECT.... ) AS 来源 ON 1 = 2 当不匹配时，则 插入.... 输出 插入ID 到 临时表 ( 插入ID ) - Eric Carlson

你不需要使用合并操作来进行简单的插入。合并操作适用于插入/更新，但对于简单的插入而言过于复杂。Andy的输出答案对我很有帮助，并帮助消除了索引锁定问题。 - CodeMonkeyForHire

如果您要插入相关项并且需要像（FakeID，IdentityID）这样的映射表来知道哪个fakeid映射到生成的IDENTITY值，则可能需要使用带有OUTPUT的MERGE。 - JohnnyFun

Answer 1

使用2005年版本的输出语句：

DECLARE @output TABLE (id int)

Insert into A (fname, lname)
OUTPUT inserted.ID INTO @output
SELECT fname, lname FROM B

select * from @output

现在您的表变量具有您插入的所有行的标识值。

- Andy Irving

---

那么你如何更新表B呢？我的意思是，你如何将@output的每条记录与B表中的一条记录关联起来？如果你使用fname和lname作为键，则使用njr的解决方案更简单。 - munissor

@munissor，我知道这是一个旧的帖子，但是请查看有关此主题的这篇simple-talk文章。请注意“添加OUTPUT子句”部分。 - Mr Moose

@munissor 有点晚了，但你可以使用 output inserted.id, inserted.whateverColumn into @output。 - Dennis Rongo

请注意，当表A具有触发器时，此方法会失败，因为微软拒绝修复的一个错误。另一个解决方法在这里：https://dev59.com/qWYs5IYBdhLWcg3wBvb7 - Alejandro

Answer 2

仔细阅读您的问题，您只想根据表A中的新标识值更新表B。

插入完成后，只需运行更新操作...

UPDATE B
SET NewID = A.ID
FROM B INNER JOIN A
     ON (B.FName = A.Fname AND B.LName = A.LName)

这里假设FName/LName组合可以用来在表格之间进行关键匹配。如果不是这种情况，你可能需要添加额外的字段以确保记录正确匹配。

如果你没有备用键可以用来匹配记录，那么这根本没有意义，因为无法区分表B中的记录。

- njreed.myopenid.com

Answer 3

据我所知，您遇到的问题是想要将数据插入到具有标识列的A表中，并且希望保留来自没有标识列的B表的标识。

为了解决这个问题，您只需要在A表上启用标识插入即可。这将允许您在插入时定义ID，只要它们不冲突，就应该没问题了。然后您可以执行以下操作：

Insert into A(identity, fname, lname) SELECT newid, fname, lname FROM B

不确定您使用的是哪种数据库，但对于 SQL Server，打开标识插入的命令将是：

set identity_insert A on

- Cory

---

他不是在尝试更新A表，而是在尝试更新B表。B表没有标识列。 - njr101

Answer 4

我建议使用uniqueidentifier类型而不是identity。在这种情况下，您可以在插入之前生成ID：

update B set NewID = NEWID()

insert into A(fname,lname,id) select fname,lname,NewID from B

- Dmitry

Answer 5

如果您希望始终出现这种行为，可以在TableA上放置一个AFTER INSERT触发器，以更新表B。

- mbelly

Answer 6

你可以通过加入行号来获取它。这是可能的，因为它是一个标识，随着你添加项目，它将自动递增，并且将按照你选择它们的顺序。

- Darren Kopp

---

记录并不保证按照你认为的顺序进入数据库。 - HLGEM

Answer 7

-- first create a table for show how its works
CREATE TABLE [dbo].[myTable]
  (
     [id]   [INT] IDENTITY(1, 1) NOT NULL,
     [text] [VARCHAR](10) NULL
  )
ON [PRIMARY]

GO

-- var table for keep new inserted id
DECLARE @tblNewInserted TABLE
  (
     newids INT
  )

--use the output clause in insert statement
INSERT INTO [dbo].[myTable]
output      inserted.id
INTO @tblNewInserted
VALUES      ('aa'),('bb'),('cc')

SELECT *
FROM   @tblNewInserted 

- R.Akhlaghi

Answer 8

MBelly说得很对 - 但是触发器将始终尝试更新表B，即使不需要（因为您还从表C插入？）。

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

如果是我，并且时间不紧迫，我会使用游标。

- Meff