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

Question

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?

Latinist

No, SCOPE_IDENTITY() only gives you the one, latest inserted IDENTITY value. But you could check out the OUTPUT clause of SQL Server .... – Circumspection

Comment to your deleted answer: I think you should remove the the first line and change the third line to sqlBuilder.Append("OUTPUT INSERTED.autoid ");. No need to put the output in a table variable if you want the output to your client code. – Mendie

Answer 1

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! :-)

Circumspection

Is there any risk if multiple users work on the same table - can they get wrong ids (of the other users)? – Latinist

@ThorstenKraus: no, each transaction gets only their own values back – Circumspection

Inserted.KeyValue doesnt exist in SQL 2014? – Wotan

@DannyRancher: depends - the Inserted pseudo table refers to the table you're inserting into - in my example, it refers to MyTable. So if that table contains a column Keyvalue, then it'll be showing that in the OUTPUT statement. This has nothing to do with any particular SQL Server version..... – Circumspection

love this solution – Medawar