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 :

  • Rails 3.0 development for deployment on SQL-server on another operating system
  • Split row into several with SQL statement
  • Same query, same DB, different execution plans & dramatically different times to execute
  • How to replace XML variable through SQL
  • SQL Querying From XML
  • How resolve this Error Loading ModulesMySQL server has gone away for large joomla database website
  • 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)

  • Problems connecting to .sdf database through SqlConnection/SqlCeConnection
  • How do I change the date format of a DataBinder.Eval in asp.net?
  • How to use Eval() with a column name that contains a dot(.)?
  • Best way to save Excel row reference
  • How to add windows user as a SQL users in windows authentication mode
  • What is the best way to handle bc dates in .net / sql server?
  • 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 [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]
    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)
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.