SQL 插入后获取新 ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/810962/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting new IDs after insert
提问by spender
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
)
using the following insert:
使用以下插入：
insert into sometable select somefield as someval from othertable
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。SCOPE_IDENTITY()只返回插入的最后一行的 ID。
How can I get all the new IDs?
我怎样才能获得所有的新 ID？
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:
想到的一种方法是从 sometable 和 scope_identity() post-insert 中获取当前最大的标识，并使用这两个值从 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()
Is there a better pattern?
有没有更好的模式？
回答by Robin Day
Use the OUTPUT functionality to grab all the INSERTED Id back into a table.
使用 OUTPUT 功能将所有 INSERTED 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
回答by Daniel
And if you want the "control" in ADO.Net and get the ids assigned to childs and getting the ids back so that you can update your model: http://daniel.wertheim.se/2010/10/24/c-batch-identity-inserts/
如果您想要 ADO.Net 中的“控制”并获取分配给孩子的 ID 并取回 ID 以便您可以更新您的模型：http: //daniel.wertheim.se/2010/10/24/c-批次身份插入/
回答by Jalpesh Vadgama
User this stored Procuedure
使用这个存储的程序
this will be a dynamic primary key..
这将是一个动态主键..
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_BulkInsertCountry
(
@FilePath varchar(1000)
)
AS
BEGIN--PROCEDURE
--variable declaration
declare @SQL varchar(500)
declare @id int
declare @CountryName varchar(30)
--Create temporary table for Country
CREATE TABLE #tmpCountry
(
CountryName varchar(30),
)
---executing bulk insert on temporary table
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
For More details visit following link http://jalpesh.blogspot.com/search?q=bulk+insert
有关更多详细信息，请访问以下链接 http://jalpesh.blogspot.com/search?q=bulk+insert
回答by Jalpesh Vadgama
create a table to set all the new IDs. then make a loop for all the insert. inside the loop make the insert you want with SCOPE_IDENTITY(). after the insert get the new ID and insert it into the new table you created for. in the end select * from [newTable].
创建一个表来设置所有新的 ID。然后为所有插入做一个循环。在循环内使用 SCOPE_IDENTITY() 进行所需的插入。插入后获取新 ID 并将其插入到您创建的新表中。最后从 [newTable] 中选择 *。
