1

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.

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
user204588
  • 1,613
  • 4
  • 31
  • 50
  • How this doesn't work? Is there any errors? Do you got unexpected results? Can you please explain. – Mahmoud Gamal Oct 19 '12 at 02:20
  • I can't use the TMMYId in the OUTPUT because it comes from the SELECT statement and is not inserted into the Class1,Class2,Class3 tables but I need to link the fields from the source DB – user204588 Oct 19 '12 at 02:27
  • If you can upgrade to SQL Server 2008 you can [use merge instead](http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id) – Mikael Eriksson Oct 19 '12 at 05:08

0 Answers0