title: Creating new elements and attaching them to a parent element via a join table in SQL
body: <p>I'm migrating a database model where I have to change a <code>1:n</code> relation to a <code>n:m</code> relation.</p>
  
  <p>I need to <code>INSERT</code> the data into the new table and use the <code>ID</code> that is generated in the process to fill the join table.</p>
  
  <p>The tables are called <code>Parts</code> and <code>Document</code> and the join table between them called <code>PartDocument</code>.</p>
  
  <p>Now I want to create two unique <code>documents</code> (with default types/names/descriptions) for each <code>part</code>, and link them to the corresponding <code>part</code> via the join table. I can create the 2*N <code>documents</code> easily, but I'm having difficulty figuring out how I would link each one to the <code>PartDocument</code> join table.</p>
  
  <pre><code>INSERT INTO Document (Type, Name, Description)
  SELECT 1, 'Work Instructions', 'Work Instructions'
  FROM Parts
  GO
  
  INSERT INTO Document (Type, Name, Description)
  SELECT 2, 'Drawing', 'Drawing'
  FROM Parts
  GO
  
  INSERT INTO PartDocument (PartID, DocumentID)
  ?????
  </code></pre>
  
  <p>My <code>PartDocument</code> join table just has two columns, <code>PartID</code> and <code>DocumentID</code>, which are used together as a composite key.</p>
  
  <p>My desired result is that I will have two documents for each part, and they will each be linked with a corresponding part via the join table.</p>
  
  <p>I'm using SQL Server Express 2012.
  <a href="http://sqlfiddle.com/#!6/b51f0" rel="nofollow">http://sqlfiddle.com/#!6/b51f0</a></p>