question

johnstrez77 avatar image
johnstrez77 asked

Output from multiple identity inserts into another table

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. 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 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
sql-server-2005
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

johnstrez77 avatar image johnstrez77 commented ·
@Dave Morrison. The problem is the TMMYID. It isn't being inserted into the Class tables so I can't use the INSERTERD. variable in this way. If I don't include the TMMYID, I'm not sure how to tie together the three new identity id's that get created.
0 Likes 0 ·
Dave Morrison avatar image Dave Morrison commented ·
Oh so you need to tie the old ID's to the new ones? A quick and dirty fix would be simply add a dummy column to the destination table so you could insert the old ID's and hence return it from the OUTPUT clause. Then simply drop the column after you're done. If this isn't possible then you'll need to do something like create a new table in the destination DB with the same structure as the destination table plus an extra column for the old ID. Then seed the identity column in the new table to start at the max identity of destination table. Do the inserts into this temp destination table then simply set identity inserts to on for the destination table and insert the rows from the temp table. Does this make sense?
0 Likes 0 ·
johnstrez77 avatar image johnstrez77 commented ·
Yes, so it sounds like adding a temp column is probably the way to go. I was trying to avoid cursors but should I in this case? Do you think it would be better just to loop?
0 Likes 0 ·
Dave Morrison avatar image Dave Morrison commented ·
Generally cursors should be avoided, however if this is a one off and it just needs to be quick and dirty then I guess its not the end of the world. However as I say, if you can do it in a set based way then I'd do it that way, if for nothing more than getting in the habit of best practice
0 Likes 0 ·
johnstrez77 avatar image johnstrez77 commented ·
OK, thanks. I'll mark your answer correct. Do you want to move you're comment into the answer?
0 Likes 0 ·
Show more comments
Dave Morrison avatar image
Dave Morrison answered
Oh so you need to tie the old ID's to the new ones? A quick and dirty fix would be simply add a dummy column to the destination table so you could insert the old ID's and hence return it from the OUTPUT clause. Then simply drop the column after you're done. If this isn't possible then you'll need to do something like create a new table in the destination DB with the same structure as the destination table plus an extra column for the old ID. Then seed the identity column in the new table to start at the max identity of destination table. Do the inserts into this temp destination table then simply set identity inserts to on for the destination table and insert the rows from the temp table. Does this make sense?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave Morrison avatar image
Dave Morrison answered
Hi John, Unless I'm missing something, cant you just use the OUTPUT clause on the insert into the destination table? So select from the source tables, insert into the destination table using the OUTPUT clause to save the new identity values to a single column table variable then insert the data of the table variable into the required place?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.