"RJN" wrote:

> 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("CategoryName"))
>
> Is this OK? or is there a better way
>
> Regards
>
> RJN
>
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***