I will import many data rows from a csv file into a SQL Server database (through a web application). I need the auto generated id value back for the client.
我将从csv文件中导入许多数据行到SQL Server数据库中(通过web应用程序)。我需要为客户端返回自动生成的id值。
If I do this in a loop, the performance is very bad (but I can use SCOPE_IDENTITY() without any problems).
如果我在循环中这样做，性能非常糟糕(但我可以使用SCOPE_IDENTITY()而不存在任何问题)。
A more performant solution would be a way like this:
更有效的解决方案应该是这样的:
INSERT INTO [MyTable]
VALUES ('1'), ('2'), ('3')
SELECT SCOPE_IDENTITY()
Is there any way to get all generated IDs and not only the last generated id?
是否有办法获得所有生成的id，而不仅仅是最后生成的id?
Thanks for your help!
谢谢你的帮助!
Best regards, Thorsten
最好的问候,托尔斯滕
1 个解决方案
#1
34
No, SCOPE_IDENTITY() only gives you the one, latest inserted IDENTITY value. But you could check out the OUTPUT clause of SQL Server ....
不，SCOPE_IDENTITY()只提供一个最新插入的标识值。但是你可以查看SQL Server的输出条款....
DECLARE @IdentityTable TABLE (SomeKeyValue INT, NewIdentity INT)
INSERT INTO [MyTable]
OUTPUT Inserted.Keyvalue, Inserted.ID INTO @IdentityTable(SomeKeyValue, NewIdentity)
VALUES ('1'), ('2'), ('3')
Once you've run your INSERT statement, the table variable will hold "some key value" (for you, to identify the row) and the newly inserted ID values for each row inserted. Now go crazy with this! :-)
一旦运行了INSERT语句，表变量将保存“某些键值”(对于您来说，用于标识行)和每个插入行的新插入的ID值。现在疯狂地做这件事吧!:-)
#1
34
No, SCOPE_IDENTITY() only gives you the one, latest inserted IDENTITY value. But you could check out the OUTPUT clause of SQL Server ....
不，SCOPE_IDENTITY()只提供一个最新插入的标识值。但是你可以查看SQL Server的输出条款....
DECLARE @IdentityTable TABLE (SomeKeyValue INT, NewIdentity INT)
INSERT INTO [MyTable]
OUTPUT Inserted.Keyvalue, Inserted.ID INTO @IdentityTable(SomeKeyValue, NewIdentity)
VALUES ('1'), ('2'), ('3')
Once you've run your INSERT statement, the table variable will hold "some key value" (for you, to identify the row) and the newly inserted ID values for each row inserted. Now go crazy with this! :-)
一旦运行了INSERT语句，表变量将保存“某些键值”(对于您来说，用于标识行)和每个插入行的新插入的ID值。现在疯狂地做这件事吧!:-)