插入后获取新的ID

Question

我正在向一个定义如下的表中插入大量新行：

CREATE TABLE [sometable](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [someval] sometype NOT NULL
)

使用以下插入语句：

insert into sometable select somefield as someval from othertable

当我完成后，我想知道所有新插入行的ID。SCOPE_IDENTITY()只返回最后插入行的ID。

我怎样才能获得所有新的ID？

一个想到的方法是从sometable中获取当前最大的标识和插入后的scope_identity()，然后使用这两个值从sometable中进行选择。例如：

declare @currentMaxId int;
select @currentMaxId=MAX(id) from sometable
insert into sometable select somefield as someval from othertable
select * from sometable where id>@currentMaxId and id<=SCOPE_IDENTITY()

有更好的设计模式吗？

- spender

Answer 1

使用 OUTPUT 功能，将所有已插入的 Id 捕获并返回到一个表中。

CREATE TABLE MyTable
(
    MyPK INT IDENTITY(1,1) NOT NULL,
    MyColumn NVARCHAR(1000)
)

DECLARE @myNewPKTable TABLE (myNewPK INT)

INSERT INTO 
    MyTable
(
    MyColumn
)
OUTPUT INSERTED.MyPK INTO @myNewPKTable
SELECT
    sysobjects.name
FROM
    sysobjects

SELECT * FROM @myNewPKTable

- Robin Day

---

@Robin - 我已经使用SQL Server工作多年了，但是我从你的答案中学到了两件新事物，OUTPUT和TABLE数据类型。谢谢。 - Corey Trager

我也使用SQL大概15年了，但不知道OUTPUT的存在，谢谢！ - Bob Radu

@Robin，这个问题可能听起来很傻，但如果我在循环（在这种情况下是游标）中检索表“myNewPkTable”中的主键值，那么这个值将被插入到该表中的一行中，对吗？ - DanielV

这是解决方案......直到表包含触发器，然后你会遇到一个微软拒绝修复的错误（在带有触发器的表中不能使用OUTPUT而不使用INTO，并且在使用INTO时给出错误值）。 解释和可能的解决方法（至少针对UPDATE情况）列在此处：https://dev59.com/qWYs5IYBdhLWcg3wBvb7 - Alejandro

Answer 2

在 @Robin Day 的回答的基础上，您可以使用 MERGE 技巧将临时 Id 映射到新 Id。

DECLARE @Keys TABLE (TempId INT, RealId INT)

MERGE MyTable
    USING @NewStuff t
    ON 1 = 0
    WHEN NOT MATCHED THEN
        INSERT (col1, col2, col3)
        VALUES (t.col1, t.col2, t.col3)
        OUTPUT t.TempId, Inserted.RealId INTO @Keys

从@Ivan Starostin在这里的聪明回答： SQL Server中插入ID的列表

- Dana

Answer 3

如果你想在ADO.Net中控制子项的ID并获取它们的ID以便更新你的模型，可以参考以下链接：

http://daniel.wertheim.se/2010/10/24/c-batch-identity-inserts/

- Daniel

---

链接已失效。网站仍然可以通过https访问，但是网站历史记录不超过2010年11月6日。 - Randy Burden

谷歌缓存版本 https://webcache.googleusercontent.com/search?q=cache:iVqxMunQvOMJ:https://danielwertheim.wordpress.com/2010/10/24/c-batch-identity-inserts/+&cd=1&hl=en&ct=clnk&gl=us - dynamiclynk

Answer 4

使用这个存储过程

这将是一个动态主键..

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

创建存储过程 sp_BulkInsertCountry ( @FilePath varchar(1000) ) AS BEGIN--PROCEDURE --变量声明 declare @SQL varchar(500) declare @id int declare @CountryName varchar(30)

--为 Country 创建临时表 CREATE TABLE #tmpCountry ( CountryName varchar(30), )

---在临时表上执行批量插入 SET @SQL='BULK INSERT #tmpCountry from ''' + @FilePath + ''' WITH (FIELDTERMINATOR ='','',ROWTERMINATOR=''\n'')' EXEC(@sql)

DECLARE cursor_Country CURSOR READ_ONLY FOR select [CountryName] from #tmpCountry

OPEN cursor_Country FETCH NEXT FROM cursor_Country INTO @CountryName WHILE @@FETCH_STATUS=0 BEGIN SELECT @id=isnull(max(Countryid),0) from tblCountryMaster SET @id=@id+1 INSERT INTO tblCountryMaster values(@Id,@CountryName) FETCH NEXT FROM cursor_Country INTO @CountryName END CLOSE cursor_Country DEALLOCATE cursor_Country END--PROCEDURE

GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO

更多详情请访问以下链接http://jalpesh.blogspot.com/search?q=bulk+insert

- Jalpesh Vadgama

Answer 5

创建一个表来设置所有新的ID。 然后为所有插入操作创建一个循环。 在循环内部使用SCOPE_IDENTITY()进行所需的插入操作。 在插入完成后，获取新的ID并将其插入到您创建的新表中。 最后从[newTable]中选择*。

- SQLMaster