title: How to get an Identity column value (not committed yet) inside a transaction
body: <p>Inside a TSQL transaction I have an operation of inserting a record into a <code>MyTable1</code> and an operation of updating a <code>MyTable2</code> with a value of <code>Identity</code> column of the <code>MyTable1</code> record, which is only to be inserted after the transaction is commited.<br> <strong>So, how to get this autogenerated value before it is actually inserted into the table?</strong></p>
  
  <p>Code just to illustrate the question:</p>
  
  <pre><code>CREATE TABLE MyTable1(
  MyTable1Id int identity(1,1) primary key,
  Field1 varchar(50)
  ) 
  
  CREATE TABLE MyTable2(
  MyTable2Id int identity(1,1) primary key,
  MyTable1Id int
  )
  
  
  CREATE PROCEDURE MyProc(
     @MyVal1  [varchar](50)
  )
  AS
  BEGIN TRAN
  -- try to insert
  INSERT INTO MyTable1(
     Field1
  )
  SELECT @MyVal1
  
  IF @@ERROR &lt;&gt; 0
  BEGIN
    ROLLBACK TRAN
  END
  
  -- update MyTable2
  DECLARE @TheUnknownIdValue int
  UPDATE MyTable2
  SET    MyTable1Id = @TheUnknownIdValue -- how to get the value needed here?
  WHERE ...
  
  IF @@ERROR &lt;&gt; 0
  BEGIN
    ROLLBACK TRAN
  END
  
  COMMIT TRAN 
  </code></pre>