January 6, 2004 at 5:14 pm
I have a tab delimited file that is not relational. I need to split each row in the file into two relational tables. The tab file looks like this:
name otherinfo grade1 grade2 grade3 grade4
What I need to do is sep. name and otherinfo into one table, stamp a unique recordID number on it and then move the grade1 grade2 etc.. into another table with the unique key we just stamped on table one into four seperate rows. It will look like this
recordID(we just created in other table) grade
1 A
2 B
3 C
4 D
What is the easiest way to do this in DTS.
Thanks in advance.
January 6, 2004 at 5:51 pm
I'd do this in two steps in DTS. First import this into a table that just moves the data. Easier this way.
Then setup a stored procedure that selects the top 1, inserts the row into tableA and gets the record ID. Then perform the other inserts into the second table.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 7, 2004 at 2:43 am
I would do the following
Create a staging table (RecordID int IDENTITY(1,1), name, otherinfo, grade1, grade2, grade3, grade4)
Create a single DTS package to
1. Truncate staging table
2. RESEED if required
3. Import data to staging table
4. Insert RecordID, name, otherinfo into first table
5. Insert RecordID, grade1 into second table
6. Repeat for grade2, grade3, grade4
Far away is close at hand in the images of elsewhere.
Anon.
January 7, 2004 at 9:25 am
Thanks all, the staging table is what I am currently doing. I was just wondering if there was a better way of if I was missing something. Thanks for the help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply