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:

  • Split SQL query into a nested query (subquery)
  • Combining a query displays incorrect results
  • Does or does not SQL Azure support CLR assemblies?
  • Retrieving item with multiple tags appended
  • Insert multiple rows in Database table using T-sql
  • how to get 2 words with SUBSTRING in SQL SERVER
  • 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

  • Backward Compatibility problems with old Microsoft SQL Server 2000 DTS package files
  • How to create an SQL table and and populate it with Excel spreadsheet data?
  • SQL Server 2000 DTS Package Failing with “The number of failing rows exceeds the maximum specified”
  • How do I start the dts transfer program?
  • SQL Server 2000 Script to list all Meta Data Services DTS packages
  • SQL Server Import and Export Wizard Error: “Index was outside the bounds of the array” via 32bit ODBC data source?
  • 2 Solutions collect form web for “SQL Server: importing from Excel, only want the new entries”

    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.

    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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.