Prepare and import data into existing database
- by Álvaro G. Vicario
I maintain a PHP application with SQL Server backend. The DB structure is roughly this:
lot
===
lot_id (pk, identify)
lot_code
building
========
buildin_id (pk, identity)
lot_id (fk)
inspection
==========
inspection_id (pk, identify)
building_id (fk)
date
inspector
result
The database already has lots and buildings and I need to import some inspections. Key points are:
It's a one-time initial load.
Data comes in an Excel file.
The Excel data is unaware of DB autogenerated IDs: inspections must be linked to buildings through their lot_code
What are my options to do such data load?
date inspector result lot_code
========== =========== ======== ========
31/12/2009 John Smith Pass 987654X
28/02/2010 Bill Jones Fail 123456B
