title: Output from multiple identity inserts into another table
body: <p>I'm trying to take the data from multiple tables and import them into other tables. The destination use identity id's and the source tables have different id's. I need to get the new identity id's from the inserts and add them to another table in the destination DB. Here is what I have so far but not sure how to get it to work. I tried adding the TMMYId to the OUTPUT but that isn't working.</p>
  
  <pre><code>DECLARE @MakeModels TABLE (MakeId int, ModelId int, YearId int, TMMYId int)
  
  INSERT INTO @MakeModels (MakeId, ModelId, YearId, TMMYId)
  SELECT MakeID, ModelID, YearID, T.id TypeMakeModelYear T 
  JOIN Fitment F 
  ON F.TMMYID = T.id JOIN PartToAppCombo P ON F.PartToAppComboID = P.ID
  WHERE P.PartmasterID IN (SELECT PP.PartMasterID FROM PartPriceInv PP WHERE Discontinued   
  = 0)
  
  DECLARE @MakeOutput TABLE (MakeId int, TMMYId int)
  DECLARE @ModelOutput TABLE (ModelId int, TMMYId int)
  DECLARE @YearOutput TABLE (YearId int, TMMYId int)
  
  INSERT INTO Class1 (Name, Active) 
  OUTPUT INSERTED.Class1Id, MM.TMMYId INTO @MakeOutput
  SELECT M.Description, 1 FROM Makes M JOIN @MakeModels MM ON M.id = MM.MakeId
  WHERE NOT EXISTS (SELECT Class1Id FROM Class1 WHERE Name = M.Description )
  
  INSERT INTO Class2 (Name, Active) 
  OUTPUT INSERTED.Class2Id, MM.TMMYId INTO @ModelOutput
  SELECT M.Description, 1 FROM Models M JOIN @MakeModels MM ON M.id = MM.ModelId
  WHERE NOT EXISTS (SELECT Class2Id FROM Class2 WHERE Name = M.Description)
  
  INSERT INTO Class3 (Name, Active) 
  OUTPUT INSERTED.Class3Id, MM.TMMYId INTO @YearOutput
  SELECT M.Description, 1 FROM Years M JOIN @MakeModels MM ON M.id = MM.YearId
  WHERE NOT EXISTS (SELECT Class3Id FROM Class3 WHERE Name = M.Description)
  
  INSERT INTO MMY (class1Id, class2Id, class3Id) 
  SELECT M.MakeId, MO.ModelId, Y.YearId FROM @MakeOutput M JOIN @ModelOutput MO ON    
  M.TMMYId = MO.TMMYId
  JOIN @YearOutput Y ON MO.TMMYId = Y.TMMYId
  </code></pre>