Import data from a flat file/import table into normalised tables of a sql express db

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!
bexcoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gothamiteCommented:
You can use the T-SQL BULK INSERT command to load this sort of data straight into a table - see snippet.
BULK INSERT database.schema.table
   FROM 'c:\path_to_file\file.txt'
   WITH 
      (
         FIELDTERMINATOR ='|',
         ROWTERMINATOR ='|\n'
      )

Open in new window

0
bexcoAuthor Commented:
Thanks for your reply gothamite!

I have come across the BULK INSERT syntax and have managed to do some successful test imports to the flat import/holding table.  It's from here that my problem, or shall I say 'challenge' manifests itself...I need to take this flat data from this import table and 'normalise it out' into the parent and respective child tables, held together by some ID numbers linking the respective parent/child tables together

How best can I go about doing that in SQL Server??
Similar to the question here

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_20770871.html?sfQueryTermInfo=1+data+flat+mani+normalis+sql+tabl

...but looking for a 2010 IT solution possibly through SQL??

Thanks again :)
BEXCO
0
gothamiteCommented:
Oh ok i understand now.

So you have a totaly de-normalised table e.g.

CompanyID CompanyName CustomerID CustomerName
1                  ABC co.            1                 John Smith        
1                  ABC co.            4                 John Smith      
1                 ABC co.             2                 Joe Bloggs      
2                 DEF co.              3                 David Jones    

If this is right I would.do the following...



-- create table and primary key for the parent (company) table
create table parent_company (CompanyID int not null,CompanyName varchar(40))
alter table parent_company add constraint pk_company primary key (CompanyID)

-- create table and primary key for the child (customer) table
create table child_customer (CompanyID int not null ,CustomerID int not null,CustomerName  varchar(40))
alter table child_customer add constraint pk_customer primary key (CustomerID)

-- create a foreign key on the child table to the parent table
ALTER TABLE [dbo].[child_customer]  WITH CHECK ADD  CONSTRAINT [FK_child_customer_parent_company] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[parent_company] ([CompanyID])

ALTER TABLE [dbo].[child_customer] CHECK CONSTRAINT [FK_child_customer_parent_company]

-- load unique records from the staging table into the parent table
insert into parent_company select distinct companyid, CompanyName from import

-- load unique records from the staging table into the child table
insert into child_customer select distinct companyid, customerid, customername from import

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

bexcoAuthor Commented:
gothamite, this is indeed my 'challenge'!

My DB is normalised in this way, through the use of PK and foreign keys on the tables!

I see that you would make use of a couple INSERT statements for the relevant tables?  I will apply my mind here so as to work with your solution and have it work in scenarios where I do not truncate the contents from the staging/import table after each normalisation 'run' of the data.
Eg: I want to keep appending data to the import table with each consecutive flat file import yet have the INSERT statements not try and insert the entire contents of the staging/import table each time but rather the 'new' data that has not been normalised into the parent-child tables at that stage!

Would you consider the use of database triggers placed onto the IMPORT or PARENT tables??
A thought below...

CREATE TRIGGER import AFTER INSERT ON import
FOR EACH ROW BEGIN
INSERT INTO parent SET put_my_fields_here;
UPDATE import SET my_flag = "done" WHERE id = whatever_my_insert_into_parent_id_was;
END;

I just don't know how a BCP/BULK INSERT operation of flat data into my 'flat' import table would work with a trigger?  I assume it would simply fire off the triggers INSERT statement for each of the new rows imported??

Thanks for your ongoing assistance!
BEXCO
0
gothamiteCommented:
Well you can set triggers to fire or not when using BULK INSERT command

Here's the example given in the books online:

BULK INSERT AdventureWorks2008R2.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
        FIELDTERMINATOR =' |',
        ROWTERMINATOR = ':\n',
        FIRE_TRIGGERS
      )

I think by default it won't fire triggers so you'll have to use this option.

I think that approach should work although it might perform badly if you have *lots* of data being imported.
0
bexcoAuthor Commented:
I will only be importing 100 or so rows per day so the volumes aren't huge so I could probably get away with firing triggers at bulk insert time.  I had not come across the FIRE_TRIGGERS syntax so thanks for that!

I will spend some more time now building the INSERT statements required so as to move the data from the flat import table and into the required parent-child tables!  I'll just have to see how I can only insert the newly imported rows of the import table each time and not the whole contents of that table as I communicated earlier.

Thanks, will let you know how I go with your solution!
0
gothamiteCommented:
another thought - if you are prepared to keep the import table forever you could just create views based on the queries I put in those INSERT statements i.e.

CREATE VIEW vw_parent_company AS SELECT DISTINCT companyid, CompanyName FROM import

this would mean these views always contain up to date data. The only downside would be that you would not be able to insert into the view i.e. they would be readonly.

You could create constraints on the import table as self-referencing foreign keys in order to maintain integrity.

Good luck!
0
bexcoAuthor Commented:
Hi All,

An update from our side...

Our current design makes use of a staging table on our database.  Once the data has been taken from the flat file and imported into the flat file we use a series of views to partition/select the data that we require.  From this view we then insert the data into the subsequent database 'normalised' tables, starting from the parent and working our way through the logic.

Essentially:
Upload flat file data into a holding table (schema is the same as the flat file itself)
Use views to select certain data elements...
Insert the results of the view into the parent tables...
Once the parent tables are populated, insert the data into the child tables and so on until such time as all of the data is 'normalised'.

We have also made use of identity seeds so as to assist with the normalising process and record tracking etc...

gothamite...points awarded to you for your continuous efforts in helping us out!

Cheers
BEXCO
0
bexcoAuthor Commented:
Solution also made use of data 'flags' on the holding table, as well as identity seeds and their subsequent usage.  Much research was required and not all documented in this solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.