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 |
|
kin
Starting Member
2 Posts |
 Posted - 2002-10-08 : 08:34:31
|
| Hi all,I'm doing a project that I need to import a text file to a SQL table.After using DTS transformation, I found that record order in the SQL table is not the same as the the record order in the text file. The record order is very important to me. I've tried to sort the record in the table but the order is not exaclty the same...Anybody know a way to keep the record when using DTS transformation?ThanksKin |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-10-08 : 08:52:10
|
| There is no such thing as 'record order' inherent to a relational database. When you select from a table, there is no rule about how the records will be ordered in the set. The physical ordering on the disk should be transparent to the RDBMS.You will need to create you staging table with a RowNum column (maybe use IDENTITY) and itereate through you text file line by line, if you want to later be able to use an ORDER BY clause to order a resultset like your file. Or, if there is something intrinsic to you source data you can ORDER BY, you could do that...Jay White{0} |
 |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-10-08 : 12:39:52
|
| As page points out, there is no order of entries in a relational DB. Depending on the size of your text file you have a couple options.1. Find a natural key to sort on. Alphabetical? A date? some number in there?2. For a smaller text file, go through and manually add a number infront of all records. Very fun... 3. As page suggested use a staging table and order it yourself by adding an identity coloumn to it.I'm actually very curious as to why the order would be important...-----------------------SQL isn't just a hobby, It's an addiction |
|
|
|
kin
Starting Member
2 Posts |
Posted - 2002-10-08 : 21:08:39
|
| I will try to use a seq. value to sort the tableThe order is important because the record are group together by some fields. I need to work with the "order of the record". I've try to sort them in SQL table but the result not the same.I need to perform reconciliation process at last.So if the record order is the same, it is easier for me to do so.thanks for replies, they are helpful.Regards,Kin |
|
|
|
|
|
|
|
|
Record order question about DTS