update pk in table1 and carry it to update fk in table2

Question

update pk in table1 and carry it to update fk in table2

Matthew Cushing

SSCrazy

Points: 2898
More actions
July 23, 2012 at 11:01 am

#264134

I have a process where I have to copy rows from two tables and update a few columns, one of which is the pk of table1, which has an fk to table2. Table1 is a portfolio and table2 is the detail that goes along with it.
What I'm trying to do is copy everything from the nightly job before, give them new keys(pk/fk, update_date, and one or two others) and insert back into the tables. The problem is, the pk is not set to autoincrement, it grabs it from a table and records what it took, etc. There's a stored proc for that.
My question is, can I use a stored proc call in an insert statement, or should I write a function to do the same thing? The other thing is, how do I pass that to the detail table for insert?
Do I need to go through table1 one row at a time, update the keys, and then do a lookup on the old pk so I can update the detail table2?
I feel like there's something simple I'm missing. I feel like I can do a select stmt to get the rows in table1 and iterate through those rows, giving them a new pk and while inside that iteration, iterate through the detail based on the old pk and update those. Maybe two iterations, the outer for table1 and the inner for table2.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

Without DDL, sample data, and expected results based on the sample data it's a little hard to provide specifics. What you may want to look at as part of the update to the parent table is the OUTPUT CLAUSE and using a table variable to capture the data you need to populate the child table.

Matthew Cushing SSCrazy Points: 2898 More actions · Answer 2

That's what I was thinking, but I felt like I was just making a random guess. I'm mostly looking for advice for which direction to head rather than specifics which is why I didn't post any details. Plus, I'm really wary about posting any code, ddl stuff or anything since I just started a new job 🙂 Dunno how they feel about all that.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 3

Matthew Cushing (7/23/2012)
That's what I was thinking, but I felt like I was just making a random guess. I'm mostly looking for advice for which direction to head rather than specifics which is why I didn't post any details. Plus, I'm really wary about posting any code, ddl stuff or anything since I just started a new job 🙂 Dunno how they feel about all that.

That can be an issue. One way around that is put together something that mimics the problem domain, but doesn't use the actual tables and data. You just need to figure out how to do that so that you can then translate any answers back to what you are attempting.

Nothing wrong with that, as long as your sample actually does mirror the problem correctly. Definately requires some work, but you will get tested answers in return for your efforts.

Matthew Cushing SSCrazy Points: 2898 More actions · Answer 4

I'm wondering if something like this might work.

select * into #temp1 from table1 where update_date = today

select * into #temp2 from table2 where pos_id in (select pos_id from #temp1)

and then drop a new column onto #temp1 assigning it a new key, and updating the key on #temp2 where #temp1.pos_id = temp2.pos_id

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

Really don't have enough information to help you at this point. I am really more visually oriented, in that if I can see what needs to be done, I can usually figure out a way to do it.