sql - Daily Bulk Loading and Updating of MySQL DB -

sql - Daily Bulk Loading and Updating of MySQL DB -

i working build partial re-create of db used reporting. build sec db in mysql. mysql db have 2 main tables. 1 table have 100 new rows need added each day , 300 need updated. table have 20 columns of ~20 character text fields. sec table have 1,000 new rows each day , 3,000 updates. table have 40 fields half beingness date fields , other have beingness ~ 10 character text fields.

each day receive tab delimited file has combined updated , new rows in it.

here ways thinking of doing updates , inserts. there options missing should looking at? advice on best approach?

option 1 - split file inserts , updates. utilize file alternative on insert load them. build , run update statements update.

option 2 - load entire file temp table utilize bring together find , update rows , insert new rows.

option 3 - build inserts on on duplicate key update

my updates , inserts info non-read operation on info each day. want automate process runs overnight.

this not big amount of operations per day. options work fine.

options 1 or 3 simplest , hence to

be implemented error free be easy future maintainer

option 3 not straight portable other databases, should ever matter.

mysql sql database