Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
akmrfleck
Starting Member
2 Posts |
 Posted - 2004-01-06 : 19:02:57
|
| 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 grade4What 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 thisrecordID(we just created in other table) grade1 A2 B3 C4 DWhat is the easiest way to do this in DTS.Thanks in advance. |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-01-06 : 20:49:16
|
| Here's one approach. Assuming that you're planning to use meaningless keys (and that's a debate I won't start right now), you could import the whole file into a working table with an IDENTITY key and all the fields in your file. You now have keys assigned and all of your data "related". Next define your two destination tables, the first one with and IDENTITY the other without. Set IDENTITY_INSERT ON and Do an INSERT INTO NewTable1 (MyID, ThisName, OtherInfo) SELECT ... FROM WorkTable.And then do similarly to insert the grades into the child table, again using the MyID from your work table, which matches the MyId that you put into the Parent Table because they all came from the same work table row.But I would normalize the child table into something like MyID, SequenceNumber, and Grade and have 4 rows rather than one with 4 columns of repeating data.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
 |
|
|
|
|
|
Transferring info from one tab file to multipe tab