Insert Parent Child records...

Hello,

We have a complex functionality of migrating data from a single record into multiple parent child tables.

To give you an example, lets us assume that we have a single table src_orders in the source database. We have a parent Order table and a child OrderDetails table in the target database. We need to pick one row from src_orders and insert this row in the Order table, pick up its PK (which is an identity column) and then use this to insert rows (say 5) in the OrderDetails table.

Again, we go back to the source, take a row, insert it into Orders, pick up the Orders PK and insert n rows in OrderDetails.

As of now, we are using the following approach for achieving this functionality.

1. Get the identity generated from the target table and store both the source table id and the target table id in a recordset.

2. Use the recordset as the source to a foreachloop , using foreachADO enumerator

3. Use data flow tasks to get the fields from the parent table for the source id, that needs to be inserted into the target child table

In case I have not ended up confusing everyone, can anyone validate this or suggest a better approach? :)

Thanks,

Satya

If it aint broke, don't fix it. There's no one correct way of acheiving something.

if it were me I would want to process all of the OrderDetails at the same time and it sounds like that's not what you're doing. Like I say though, if it works for you and performs in the required time, stick with it!

-Jamie

|||

As Jamie says, SSIS is a platform tool meaning that the infrastructure typically provides many ways to solve the same problem. However, here are a couple of alternatives that may work for you if you are having issues with the current approach.

1) Wrap this functionality in a stored procedure, and call the SP for every row. The SP would insert the parent item, retrieve the @.@.identity (actually, scope_identity()) and then insert the child. Perf may suck since you're calling the SP for every single row instead of bulk operations.

2) Wrap this functionality in a ScriptTransform that does pretty much the same as the previous bullet, only in VB.Net instead of TSQL. Once again, be careful of perf.

3) Wrap this functionality in a view that utilizes an InsteadOf trigger for the inserts. There are many contraints when going down this route however so your mileage may vary. For instance I cannot recall offhand if table joins are supported in this scenario; BOL has a wealth of info.