Luis Cazares (6/12/2014)
cbrammer1219 (6/12/2014)
No I figured I would get the tsql working first, and then attempt that change.If you fix the data import, you don't need to work that hard on the t-sql.
+1 GAZILLION-TRILLION-BILLION!!!!
At the time I first looked at it, only 2 people had looked at the text file that the OP posted. As with most telephone systems, it's nothing more than a mostly [font="Arial Black"]simple fixed-field-format text file [/font]that can be easily resolved/imported/parsed either by using a BULK INSERT of the entire row and using SUBSTRING to parse the fields, or by using BULK INSERT with a BCP format file to do the parsing and most of the data validation for you. For the most part, there's nothing complicated about importing this file in a columnar fashion to a staging table for validation.
The proverbial fly-in-the-ointment is in the 5th field of the file. It contains many different types of data depending on (I would imagine) the nature of the CDR (Call Detail Record, which is what the file contains). Here's a sample of what those damned things look like and only someone with the "book" on what the record layout and field definitions of the CDRs would be able to interpret. Even though there are spaces in this field, they should NOT be interpreted as column delimiters in the overall columnar import of the CDRs. They are, however, column delimiters within the field and will need a bit of post processing. Such post processing would be determined by what "the book" says about the content of this 5th field.
1403 17815051438
2422 16176660248
T7818592700 2074
T2074 15088720422
T2074 915088720422
P105 101 101
T8*029 8*042705 2704 91781
1 17818592702
I'll be back...
--Jeff Moden
Change is inevitable... Change for the better is not.