Posted - 05/13/2014 : 13:07:15
| Hello everyone - I was wondering if I can get some feedback regarding a project.
Basically, we will need to bulk load data every night - few throusands records, no space or any other delimiter in the file. Data comes as a test file. We have a definition table wich specifies the start, lenght of each field that we need.
So no problem here - I can generate the format file using bcp and the table with definitions.
The issue I have is when inserting this data into a final table - each chunk of data must have an ID; I cannot find a way to link the data content with the correct ID to insert into a final table.
Maybe an example will help.
My definition table:
VarID Name Desc Start Lenght
1 AdmitDate Admit Date 110 6
In the file, the Admit Date will be somewhere like this:
xxxxxx01012014xxxxx - where xxx may be some other data.
So I am able to extract the data under a staging table using bcp in this format:
But I will need to insert into production using this format
RecordID VarID VarDesc VarValue
AutoGen 1 AdmitDate 01012014
How can I do that?
Thank you for your advice.