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 = ?