August 14, 2002 at 4:19 pm
I'm using sql server 2000 and am new at using dts. I have imported a flat text file into a table and I am now trying to normalize my tables.
I have a table that holds a listing of all product names and their matching IDs. I'm using the flat text file as my source. I want to copy some of the columns from the source table into the destination table but instead of using the product name from the source, I want to use the product ID from the Product table.
I used the source table and the product table to retrieve the ID within the query designer and the data that was retrieved was correct.I believe the problem is my ActiveX script within the transformation properties. The ID exists only within the destination table.
Function Main()
dim intProdInfo
intProdInfo = DTSLookups("lkp_GetID").Execute
DTSDestination("ID") = intProdInfo
Main = DTSTransformStat_OK
End Function
When testing, It looks as though the results retrieved the first value and used that same value for the number of records within my destination table.
Any help is appreciated...Thanks.
August 15, 2002 at 3:31 pm
Instead of running a lookup for each line in your text file, you'd be much more efficient to use DTS to bring the file into a table, then use SQL to load the data into the normalized tables. Here's an example:
You have three normalized tables.
School (ID int, Name Varchar(25))
Teacher (ID int, SchoolID int, Name varchar(25))
Student (SchoolID int, TeacherID int, Name varchar(25))
and one import table
Import (SchoolName varchar(25), TeacherName varchar(25), StudentName Varchar(25))
The first thing you do is import any SchoolNames that are not in your school table:
insert into School(name)
select
schoolname
from
Import I
left join
School S on I.SchoolName = S.Name
where
S.ID is null
Do the same for Teacher:
Insert Into Teacher (SchoolID, Name)
Select
S.ID, TeacherName
From
Import I
Join
School S on S.Name = I.SchoolName
left join
Teacher T on I.TeacherName = T.name and
S.ID = T.SchoolID
Where
T.ID is null
Finally bring in all your students:
Insert into Students (SchoolID, TeacherID, Name)
select
S.ID, T.ID, StudentName
From
Import I
Join
School S on I.SchoolName = S.Name
Join
Teacher T on I.TeacherName = T.Name and
S.ID = T.SchoolID
/*
You probably should do a left join on the Student table too just to make sure you don't bring in duplicate students.
*/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply