MySQL Forums
Forum List  »  Newbie

Re: Automating DB management
Posted by: Phillip Ward
Date: November 08, 2022 05:08AM

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.

Options: ReplyQuote

Written By
November 07, 2022 01:28PM
Re: Automating DB management
November 08, 2022 05:08AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.