Can I get new values ​​for Id (IDENTITY) before inserting data into a table?

Question

Can I get new values ​​for Id (IDENTITY) before inserting data into a table?

You could write something like this:

INSERT INTO Table1
SELECT *GET_NEW_IDENTITY*, Field1, Field2 FROM Table2

I need Id values ​​because I want to insert data into table1, and immediately after insert data into another table that has a foreign key associated with table 1 (with identifier)

Answer 1

IDENT_CURRENT. Returns the last identification value generated for the specified table or view. The last generated identity value can be for any session and any area.

SCOPE_IDENTITY. , . - : , , .

OUTPUT. , INSERT, UPDATE, DELETE MERGE. [...] OUTPUT INSERT UPDATE.

Answer 2

insert, .

create table demo( Id int identity primary key, data varchar(10))
go
insert into demo(data) output inserted.Id values('something')

Answer 3

, , .

, ,

SELECT newid = @@identity FROM table

INSERT

Answer 4

? Table2, SCOPE_IDENTITY(), Id 1.

Answer 5

. , , .. -. , script ,

-- run [1] 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

---

[1]

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)

Answer 6

IDENT_CURRENT ('tableName') returns the current identifier value for this table. The identifier value that will be assigned during insertion will be IDENT_CURRENT ('tableName') + IDENT_INCR ('tableName').

SELECT IDENT_CURRENT('tableName') + IDENT_INCR('tableName')