Help with data migration

[meckeard]

Hi,

I inherited a database that stores all of our telecommunication bill information (phone, pager, cell, etc) and need some help in importing the data to a newer table design.

Each bill received usually gets allocated to many departments. HR pays x amount, Marketing pays x amount, etc.
So, if a bill comes in and it gets allocated (or charged) to 5 different departments, 5 entries were made in the old table.

The table layout was flat. One table held everything. I changed it to a relational design. Now I need a little help getting the data in to the new tables.

I changed it to 2 tables. One is tblBilling (parent record), the other is tblAllocation (child records).

In the new layout, for every bill we receive, 1 record always gets inserted in to tblBilling. Then, for every department that pays a piece, I insert 1 allocation record.

Since the old table has what I now consider the allocation (or child) records, I need to create the billing record for each group and insert it in to the billing table.

This I can do. I used a "select sum(amount) into tblBilling from tblOldAllocation...group by..." to create the parent records.

What I can't do is figure out how to get the newly created PK (Billing_ID) from tblBilling back in to tblAllocation so that I can create the relationship between the billing record and the allocation records.

Any ideas?

Thanks,

Mark

 

[Patrick2002]

I'd say make sure your tblAllocation contains a field of the same datatype as your billing_ID. Then write a script (possibly using a cursor) that compares every row from your tblAllocation to the data in your tblBilling (provided you still have a column to make the match) and updates the key field in tblAllocation accordingly. When that's done, you can create the FK constraint on the two fields.
Succes