Menu
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
445
If you want to insert more rows than that, you should consider using multiple INSERT statements, BULK INSERT or a derived table. Note that this INSERT multiple rows syntax is only supported in SQL Server 2008 or later. To insert multiple rows returned from a SELECT statement, you use the INSERT INTO SELECT statement.
If you insert a row into the table, @@IDENTITY and SCOPE_IDENTITY() return different values. SCOPE_IDENTITY() returns the value from the insert into the user table, whereas @@IDENTITY returns the value from the insert into the replication system table.
The Oracle INSERT ALL statement is used to add multiple rows with a single INSERT statement. The rows can be inserted into one table or multiple tables using only one SQL command.
The @@identity function returns the last identity created in the same session. The scope_identity() function returns the last identity created in the same session and the same scope. The ident_current(name) returns the last identity created for a specific table or view in any session.
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! :-)
86
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
