插入后获取新ID [英] Getting new IDs after insert

问题描述

我正在将一堆新行插入到定义如下的表中:

I'm inserting a bunch of new rows into a table which is defined as follows:

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插入的最后一行.

when I've finished, I'd like to know the IDs of all the newly inserted rows. SCOPE_IDENTITY() only returns the ID last row inserted.

如何获取所有新ID?

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

One method that springs to mind would be to grab the current largest identity from sometable and the scope_identity() post-insert, and use these two values to select from sometable. For example:

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()

有更好的模式吗?

推荐答案

使用OUTPUT功能将所有插入的ID都重新获取到表中.

Use the OUTPUT functionality to grab all the INSERTED Id back into a table.

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

这篇关于插入后获取新ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！