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.