问题描述
我将一堆新行插入到定义如下的表中:
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?
How can I get all the new IDs?
想到的一种方法是从 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 功能将所有 INSERTED 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！