Search code examples
sql-serverdtsimport-from-excel

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

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:

alt text

Its target table is like this:

alt text

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.

alt text

Solution

  • 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.