title: How to insert master detail records in T-SQL?
body: <p>I need to copy some master-detail records, along the lines of:</p>
  
  <pre><code>INSERT INTO Order
  (
      SupplierId
     ,DateOrdered
  )
  SELECT 
     SupplierID
    ,DateOrdered
  FROM Order
  WHERE SupplierId = 10
  
  DECLARE @OrderId int;
  
  Select @OrderId = Scope_Identity;
  
  INSERT INTO OrderItem
  (
     Quantity
    ,ProductCode
    ,Price
    ,FkOrderId
  )
  SELECT 
    Quantity
   ,ProductCode
   ,Price 
   ,FkOrderId
  FROM OrderItem 
  WHERE FkOrderId = @OrderId
  </code></pre>
  
  <p>This will not work. The reason is that there are multiple Orders for Supplier = 10. So what is the best way to iterate through each Order where Supplier = 10, Add the order, and then add the relevant child OrderItem BEFORE going onto the next Order Record where supplier=10. I think I am talking about batching, possibly cursors, but I am a newbie to T-SQL / Store Procedures.</p>
  
  <p>I would appreciate advice on the above.</p>
  
  <p>Thanks.</p>
  
  <p>EDIT</p>
  
  <p>Some more information which I hope will clarify by virtue of some sample data.</p>
  
  <pre><code>Original Order Table
  
  Id       SupplierId         DateOrdered
   1          10                01/01/2000
   2          10                01/01/2000
  
  Original OrderItem Table
  
  Id       Quantity      ProductCode     Price      FkOrderId
   1          20             X1           100           1
   2          10             Y1            50           1
   3          30             X1           100           2
   4          20             Y1            50           2
  
  Final Order Table
  
  Id       SupplierId         DateOrdered
   1          10                01/01/2000
   2          10                01/01/2000
   3          10                01/01/2000    (Clone of 1)
   4          10                01/01/2000    (Clone of 2)
  
  Final OrderItem Table
  
  Id       Quantity      ProductCode     Price      FkOrderId
   1          20             X1           100           1
   2          10             Y1            50           1
   3          30             X1           100           2
   4          20             Y1            50           2
   5          20             X1           100           3 (Clone of 1, linked to clone Order=3)
   6          10             Y1            50           3 (Clone of 2, linked to clone Order=3)
   7          30             X1           100           4 (Clone of 3, linked to clone Order=4)
   8          20             Y1            50           5 (Clone of 4, linked to clone Order=4)
  </code></pre>
  
  <p>So I need some help with the code can do this cloning of Order and OrderItem to achieve the "final" table records.</p>
  
  <p>It seems I need to do something like:</p>
  
  <pre><code>For each matching record in "Order"
  
    Clone Order Record
    Clone OrderItem Record where FkOrderId = OldOrderId
  
  Next
  </code></pre>