title: How to update 2nd table with identity value of inserted rows into 1st table
body: <p>I have the following table structures</p>
  
  <pre><code>CREATE TABLE [dbo].[WorkItem](
      [WorkItemId] [int] IDENTITY(1,1) NOT NULL,
      [WorkItemTypeId] [int] NOT NULL,
      [ActionDate] [datetime] NOT NULL,
      [WorkItemStatusId] [int] NOT NULL,
      [ClosedDate] [datetime] NULL,
  )
  
  CREATE TABLE [dbo].[RequirementWorkItem](
      [WorkItemId] [int] NOT NULL,
      [RequirementId] [int] NOT NULL,
  )
  
  CREATE TABLE #RequirmentWorkItems
  (
      RequirementId int,
      WorkItemTypeId int,
      WorkItemStatusId int,
      ActionDate datetime
  )
  </code></pre>
  
  <p>I use the <code>#RequirmentWorkItems</code> table to create workitems for requirements.  I then need to INSERT the workitems into the <code>WorkItem</code> table and use the identity values from the <code>WorkItem</code> table to create the cross-reference rows in the <code>RequirementWorkItem</code> table.  </p>
  
  <p>Is there a way to do this without cursoring thru each row?  And I can't put the <code>RequirementId</code> into the <code>WorkItem</code> table because depending on the <code>WorkItemTypeId</code> the <code>WorkItem</code> could be linked to a <code>Requirement</code> or a <code>Notice</code> or an <code>Event</code>.  </p>
  
  <p>So there are really 3 xref tables for <code>WorkItems</code>.  Or would it be better to put a <code>RequirementId</code>, <code>NoticeId</code> and <code>EventId</code> in the <code>WorkItem</code> table and 1 of the columns would have a value and other 2 would be null?  Hopefully all this makes sense.  Thanks.</p>