Table Design Question

[JVZ]

I need some help/idea on designing a table.

Background/Requirements:

We import a flat file on a daily basis (with about 700,000 Records) that looks something like:

PortNo|CycleNo|SeqNum|FirstError|SecondError|ThirdError|FourthError|FifthError|TransactionDetails

Layout of the File/Table Currently:

PortNo - SmallInt
CycleNo to FifthError are all varchar(10)
TransactionDetails is varchar(210) (The data itself is Fix Width delimited - about 22 columns if broken out).

Problem:
The Application Support team wants the TransactionDetails column broken out into separate columns so that they can run queries against the Data w/o having to use the substring function.

I was wondering if someone can provide any suggestion if it would be better to break the TransactionDetails column up (during import) and create a custom import program that will insert the value into another table so that it would look something this:

ErrorInfoTbl TransactionDetailTbl
RecordID (PK) RecordID(FK)
PortNo Field1
CycleNo Field2
SeqNum Field3
FirstError ....and so
SecondError
ThirdError
FourthError
FifthError

I was thing about creating a view that will breaking the TransactionDetail columns using substring so that they can run queries W/O having to use substrings… however wouldn’t that solution have a major affect on query time? I'm looking a solution that will not have a major affect on query time, and also a way not to increase the time it take the data to import by to much....Any idea/suggestion…?
Thanks

 

[SQLSister]

Well I do this in DTS all the time. Usually I import to a holding table that is just like the structure of the text file in the first step. Then on succes of the step, I use the execute SQL task to import to the real table. In this case I would just use the substring to define which charaters you want to go to which field. so it would be something like:

Insert Table1 (col1, col2, col3)
Select col1, left(col2, 3), Substring (col2, 4, 4) from holdingtable1

Obviously it can get more complex than that But I got the impression you already knew exactly how to use substring to divide your data.

 

[JVZ]

Thank you for replying. Sorry but I have one more question. Would it be better to create one long table that with all the columns, or would be better (performance wise) to create a one - to - one relation i.e.:

ErrorInfoTbl RecordDetailTbl
RecordID (PK) RecordID (FK)
PortNo Field1
CycleNo Field2
SeqNum Field3
FirstError ....and so
SecondError
ThirdError
FourthError
FifthError

The data is used by the support staff to investigate why record failed during the life cycle of a transaction; The table(s) holds the edits that the application produced along the record that was processed.

Again thank you for you help!

 

[SQLSister]

It would be better to create related tables with a one to many relationship.

Error Info table with the fields that relate to the general error data like RecordId, PortNo, Cycle No, Seq Number

Then an Eroor Details TAble with RecordID and ErrorDescription in it. You would then put the contents of error1, error2,error3, etc in this table as separate records.

I don't know how many other fields you have once you pull out the error number ones, but if they will fit the record size limits of one table, it is better to have them in one table because performing the join takes time. However, if you have too many fields to fit on one record, then yes a table with a one-to-ont relationship is your best alternative.