Is there any way to use SCOPE_IDENTITY if using a multiple insert statement?

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.

If I do this in a loop, the performance is very bad (but I can use SCOPE_IDENTITY() without any problems).

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?

Thanks for your help!

Best regards, Thorsten

Answers


No, SCOPE_IDENTITY() only gives you the one, latest inserted IDENTITY value. But you could check out the OUTPUT clause of 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! :-)


Need Your Help

How to switch to a different buffer from a terminal buffer

emacs emacs23

I've been using emacs for few weeks and it's been great so far - coming from vim was easier than I expected (actually - emacs' keyboard shortcuts feel more... natural).

Is there a way to tell crossfilter to treat elements of array as separate records instead of treating whole array as single key?

javascript d3.js crossfilter dc.js

I have data set where some of the field values are arrays and I'd like to use crossfilter and d3.js or dc.js to display histogram of how many times each of those values was present in the dataset.