在SQL Server中,如何在完成批量更新时获得最后插入的记录ID ?

时间:2020-12-04 09:42:03

In SQL Server, i am inserting multiple records into table using batch update. How do i get back the ID's (unique primary key) which is being created after batch update?

在SQL Server中,我使用批量更新将多个记录插入到表中。如何获得在批处理更新后创建的ID(惟一主键)?

If I insert one record, I can get the last inserted using IDENT(tableName). I am not sure how to get if I do batch update. Please help.

如果我插入一个记录,我可以使用IDENT(tableName)获得最后一个插入记录。我不知道如果我做批量更新该如何获得。请帮助。

For example, I have student table, with ROLE NO and NAME. ROLE NO is auto incremented by 1, as soon I insert the names into DB using java program. I will add 3 rows at a time using batch update from my java code. In DB, it gets added with ROLE NO 2, 3 and 4. How do I get these newly generated ID in my java program, please help

例如,我有一个student表,其中包含角色NO和名称。当我使用java程序将名称插入到DB时,角色NO将自动增加1。我将使用java代码中的批处理更新一次添加3行。在DB中,添加角色2、3和4。如何在java程序中获得这些新生成的ID,请帮助

I tried getting ids using getgeneratedkeys method after I do executebatch. I get exception. Is batch update + get generated keys supported.?

在执行executebatch之后,我尝试使用getgeneratedkeys方法获取id。我例外。是否支持批量更新+生成密钥。

2 个解决方案

#1


3  

In SQL Server when you do an insert there is an extra option your query; OUTPUT. This will let you capture back the data you inserted into the table - including your id's. You have to insert them into a temporary table; so something like this (with your table/ column names will get you there.

在SQL Server中,当您进行插入时,您的查询还有一个额外的选项;输出。这将使您能够捕获插入到表中的数据——包括您的id。必须将它们插入临时表中;像这样的东西(用你的表/列名就可以了。

declare @MyNewRoles Table (Name, RoleNo)

insert into tblMyTable
(Name)
Select
    Name
Output
    inserted.Name, Inserted.RoleNo
    into @MyNewRoles
From tblMyTableOfNames

select * from @MyNewRoles

#2


1  

If you don't mind adding a field to your table, you could generate a unique ID for each batch transaction (for example, a random UUID), and store that in the table as well. Then, to find the IDs associated with a given transaction you would just need something like

如果您不介意将字段添加到表中,那么可以为每个批处理事务(例如,一个随机的UUID)生成惟一的ID,并将其存储在表中。然后,要找到与给定事务相关联的id,您只需要一些类似的东西。

select my_id from my_table where batch_id = ?

#1


3  

In SQL Server when you do an insert there is an extra option your query; OUTPUT. This will let you capture back the data you inserted into the table - including your id's. You have to insert them into a temporary table; so something like this (with your table/ column names will get you there.

在SQL Server中,当您进行插入时,您的查询还有一个额外的选项;输出。这将使您能够捕获插入到表中的数据——包括您的id。必须将它们插入临时表中;像这样的东西(用你的表/列名就可以了。

declare @MyNewRoles Table (Name, RoleNo)

insert into tblMyTable
(Name)
Select
    Name
Output
    inserted.Name, Inserted.RoleNo
    into @MyNewRoles
From tblMyTableOfNames

select * from @MyNewRoles

#2


1  

If you don't mind adding a field to your table, you could generate a unique ID for each batch transaction (for example, a random UUID), and store that in the table as well. Then, to find the IDs associated with a given transaction you would just need something like

如果您不介意将字段添加到表中,那么可以为每个批处理事务(例如,一个随机的UUID)生成惟一的ID,并将其存储在表中。然后,要找到与给定事务相关联的id,您只需要一些类似的东西。

select my_id from my_table where batch_id = ?