DTS - Suggestion on using look ups

Question

DTS - Suggestion on using look ups

Rajesh-228994

SSC Veteran

Points: 282
More actions
May 9, 2005 at 11:31 pm

#68989

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

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47797 More actions · Answer 1

Using Lookups will switch the Datapump into row-by-row processing. This will significantly degrade the throughput of the datapump.

How much data are you loading? If it's not a lot then using lookups in the method you've outlined could be ok. Otherwise I'd look and doing something at the T-SQL level using inner joins

EG:

SELECT prd.ProductID, newcat.CategoryID
FROM OldProduct Prd
INNER JOIN NewCategory NewCat
ON Prd.[Category Name] = NewCat.[Category Name]

This makes it a set based operation that, in comparrison to the row-by-row lookup method, will run very quickly.

--------------------
Colt 45 - the original point and click interface

Rajesh-228994 SSC Veteran Points: 282 More actions · Answer 2

Hi Phill

Thanks for the reply. I can't write the kind of T-SQL query you've suggested i.e, joining tables across databases inside DTS.

The max. no. of records I've in one of the tables is about 50000. So lookup should be ok then?

Thanks

Rajesh

philcart SSC-Forever Points: 47797 More actions · Answer 3

What about writing the T-SQL statement outside of DTS. Are the databases on the same server? If so, then why use DTS?

If not, then use DTS to pump the data into a temporary table then use the T-SQL statement to insert the data into its final destination. This would also allow you to scrub the data and correct any spelling errors etc...

Also, another point to bear in mind, is this a one time process? If so, then does it really matter how long it takes?

--------------------
Colt 45 - the original point and click interface

Rajesh-228994 SSC Veteran Points: 282 More actions · Answer 4

Thanks again for your reply.

The max. no. of records I've in one of the tables is about 80000. I think I'll go ahead with the DTS look up since this is going to be a one time operation. Migration will happen happen over a weekend which means the operation can take max. of 2 days.