Preparing and importing data into an existing database

I am maintaining a PHP application with a SQL Server backend. The database structure is something like 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

      

        
        
        
      

    

There are parcels and buildings already in the database and I need to import some checks. Key points:

• This is a one-time bootstrap.
• The data goes into an Excel file.
• Excel data is not aware of autogenerated DB IDs: inspections must be linked to buildings via their lot_code

What are my options for downloading such data?

date         inspector     result     lot_code
==========   ===========   ========   ========
31/12/2009   John Smith    Pass       987654X
28/02/2010   Bill Jones    Fail       123456B

      

        
        
        
      

    

Update: how I finally did it

In case someone else has to complete a similar task, these are the steps required to finally load the data:

• Prepare your Excel file: remove unnecessary columns, provide names for sheets and column headers, etc.

• C SQL Server Import / Export Wizard
  
  (32-bit, 64-bit lacks this feature), load each sheet into a (new) database table. The wizard takes care of most of the dirty details, including creating the appropriate database structure.

• Enter the database with your favorite client. To make the SQL coding easier, I have created some additional fields in the new tables.

• Start a transaction.

  BEGIN TRANSACTION;
  
  

• Update the auxiliary columns in the newly created tables:

  UPDATE excel_inspection$ SET building_id = bu.building_id FROM building bu INNER JOIN ....
  
  

• Insert data into target tables:

  INSERT INTO inspection (...) SELECT ... FROM excel_inspection$ WHERE ....
  
  

• Review the results and commit the transaction if everything is ok:

  COMMIT;
  
  

In my case, SQL Server was complaining about sort conflicts when connecting to new tables with existing ones. It was fixed by setting the appropriate collation on the new tables, but the method is different: in SQL Server 2005 I could just change the collation from the SQL Server Manager (click, click, save and execute), but in SQL Server 2008 I had to set the collation manually in the Wizard import (button "Edit SQL").