Menu
I have a table with an autoincrement id that I am doing a
INSERT INTO ( ... ) SELECT ... FROM ...
Is there a way for me to get the list of id's that have been inserted?
I was thinking I could get the max id before the insert then after and assuming everything in between is new, but then if a row gets inserted from somewhere else I could run into problems. Is there a proper way to do this?
I am using SQL Server 2005
595
Once we insert a row in a table, the @@IDENTITY function column gives the IDENTITY value generated by the statement. If we run any query that did not generate IDENTITY values, we get NULL value in the output. The SQL @@IDENTITY runs under the scope of the current session.
An identity column contains a unique numeric value for each row in the table. Whether you can insert data into an identity column and how that data gets inserted depends on how the column is defined.
An explicit value for the identity column in table 'Students' can only be specified when a column list is used and IDENTITY_INSERT is ON. In simple words, the error says that since the flag IDENTITY_INSERT is off for the Id column, we cannot manually insert any values.
IDENTITY_INSERT is a table property that allows you to insert explicit values into the column of table identifiers, i.e. into the column with IDENTITY. The value of the inserted identifier can be either less than the current value or more, for example, to skip a certain interval of values.
Use the output clause.
DECLARE @InsertedIDs table(ID int);
INSERT INTO YourTable
OUTPUT INSERTED.ID
INTO @InsertedIDs
SELECT ...
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
