SQL Server: importing from Excel, only want the new entries

Question

The task is to have SQL Server read an Excel spreadsheet, and import only the new entries into a table. The entity is called Provider.

Consider an Excel spreadsheet like this:

Its target table is like this:

The task is to:

• using 2008 Express toolset
• import into an existing table in SQL Sever 2000
• existing data in the table! Identity with increment is PK. This is used as FK in another table, with references made.
• import only the new rows from the spreadsheet!
• ignore rows who don't exist in spreadsheet

Question: How can I use the SQL 2008 toolset (Import and Export wizard likely) to achieve this goal? I suspect I'll need to "Write a query to specify the data to transfer".

Problem being is that I cannot find the query as the tool would be generating to make fine adjustments.

Answer 1

What I'd probably do is bulk load the excel data into a separate staging table within the database and then run an INSERT on the main table to copy over the records that don't exist.

e.g.

INSERT MyRealTable (ID, FirstName, LastName,.....)
SELECT ID, FirstName, LastName,.....
FROM StagingTable s
    LEFT JOIN MyRealTable r ON s.ID = r.ID
WHERE r.ID IS NULL

Then drop the staging table when you're done.

Answer 2

You can run some updates on that stage table before you load it, to clean it up, if you need to, as well. UPDATE SET NAME = RTROM(LTRIM(Name)) FROM YOUR.STAGE.TABlE for example