Re: Automating DB management

[Phillip Ward]

Split the task into smaller pieces.

You might have the capability to "pre-process" the file (with "scripts") and make the "raw" data a better "fit" for your database tables. Depends entirely on the skillset that you or others around you have.

Alternatively - and more relevantly, given that we're in a Database Forum :-) - you might prefer to do it all in the database:

First, get the data into the database.
Load it into an intermediate table that matches the file structure. Most, if not all of these columns will be the varchar Data Type, just in case, one day, the file somehow gets a CustomerId of 'q' in it!

Then, write some SQL that transforms the data from its "raw" state (table) into something that fits the database properly. Anything that doesn't "fit" can be side-lined into more tables.
If you need to do more than one transformational "hop", that's fine - it's all still happening in the database and as part of the same "Daily Load" process.

Finally, you load the cleaned, properly-"shaped" data into the real database Tables. That might need inserts or updates or something more exotic - YMMV.

Regards, Phill W.