Is is possible to get new values for Id (IDENTITY) before inserting data in a table?
Is is possible to get new values for Id (IDENTITY) before inserting data in a table ?
Is is possible to write something like that :
INSERT INTO Table1 SELECT *GET_NEW_IDENTITY*, Field1, Field2 FROM Table2
I need the values of Id because I want to insert data in Table1 and, just after, insert data in another table which has a foreign key linked to Table1 (with Id)
5 Solutions collect form web for “Is is possible to get new values for Id (IDENTITY) before inserting data in a table?”
IDENT_CURRENT. Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.
SCOPE_IDENTITY. Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch.
OUTPUT. Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. […] The OUTPUT clause may be useful to retrieve the value of identity or computed columns after an INSERT or UPDATE operation.
you can also have the insert statement return the newly inserted value for later use. for example
create table demo( Id int identity primary key, data varchar(10)) go insert into demo(data) output inserted.Id values('something')
No, because it is the act of adding a row which creates the new identity value.
To do what you want,
SELECT newid = @@identity FROM table
just after the INSERT
Why would you need to get the identity value before doing the insert? Just do the insert to Table2 returning SCOPE_IDENTITY() and then use the resulting Id value for your insert to Table1.
This is just fast demo. You can use new ID for insert for update, insert into another table, query, etc. in another way. Hoping I did not insert errors into script during formatting, editing post
-- run  before this script once to have environment --create temporary table once if not dropped after -- really only ID field is needed, the others are for illustration create table #temp_id (Id int, d1 int, d2 int) select * from Table2;-- this is read-only, filled once here source select * from Table1;--interesting for following runs insert into Table1 OUTPUT INSERTED.id -- really only ID is needed, the rest is for illustration , inserted.d1, inserted.d2 INTO #temp_id select field1, field2, null-- null to be merged later -- or inserted/updated into another table from Table2; select * from Table1; select * from #temp_id; MERGE Table1 AS TARGET USING #temp_id AS SOURCE ON (TARGET.id = SOURCE.id) WHEN MATCHED --AND OR are redundant if Table1.ID is PK THEN UPDATE SET TARGET.IDnew = SOURCE.id; select * from Table1; --drop table #temp_id --drop table table1 --drop table table2
Reproducing the tables from question and filling with data
create table Table1( Id int identity primary key, d1 int, d2 int, IDnew int) create table Table2( field1 int, field2 int) insert into table2 values(111,222) insert into table2 values(333,444)