Good Day All,
I am using a SQL Server Express 2008 database so as to store information for a call centre.
I will be receiving a daily flat file [pipe delimited data] upload containing client records. My ultimate goal is to take this 'flat' data file and import it into a 'holding' table contained in my sql database [dbo.import]. This import table will have the same schema as the flat file etc... and will serve simply as a staging area for all of the daily uploads.
Here is where my problem begins and where I am working furiously so as to get myself up to speed as quickly as I can! I need to take the data from this 'flat' import table and move the data into tables that are normalised through PK and FK relationships, ID's contained on columns etc...
Each row of flat data from the import table needs to be moved into a parent table and this parent table then contains children tables that require information and are linked to the parent as I mentioned through the use of PK's, FK's and ID numbers...
Where should I focus my time? What is the best and most accepted method of normalising a flat file into a sql database??? Program script, SQL stored procedure and triggers, BCP, SSIS??
I would prefer to do this from within SQL if possible as that is where I'm certainly stronger from a skills point of view?
I am furiously trying to find the best method and would appreciate ANY feedback on how to complete this!
Thanks in Advance!