Hi

I'm migrating data from the old schema to the new schema using DTS. The
old and new schema are different. In the new schema we have identity
columns as Primary keys and referred as foreign keys in the child
tables. In the old schema Primary keys are not identity columns. So when
I migrate old data, identity columns are newly generated for parent
tables. Now in order to establish the foreign key relationship in the
child table i.e, to map the identity value generated, I'm planning to
make use of lookups and active scripts something like this.

Old Schema:

Categories table:
Category name varchar(100) Primary key

Products table:
ProductID int PK
Category Name varchar(100)FK

New Schema:

Categories table:
CategoryID int identity Primary key,
Category Name varchar(100)

Products table:
ProductID int PK
CategoryID int FK

When migrating Categories there is no issue as the identity column
CategoryID is newly generated. When migrating Products table, I should
get the CategoryID value for corresponding Category Name. Shall I use
look ups for this?

SELECT CategoryID FROM Categories
WHERE (CategoryName = ?)

In the active-x script, I'll pass the old CategoryName value

DTSDestination("CategoryID")
=DTSLookups("GetCatID").Execute(DTSSource("Categor yName"))

Is this OK? or is there a better way

Regards

RJN




*** Sent via Developersdex http://www.developersdex.com ***

Please do not post the same question multiple times under different
headings. It only means more work in collating the answers for your
self <grin>


Allan

"RJN" <rjn (AT) yahoo (DOT) com> wrote