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:

  • SQL Server Management Studio: Increase number of characters displayed in result set
  • What is wrong with this SQL Server query division calculation?
  • Using SqlBulkCopy with MongoDB
  • Change a Primary Key from Nonclustered to Clustered
  • Bulk insert fixed width fields
  • What makes a SQL statement sargable?
  • 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

  • Upgrading DTS packages to SSIS Packages
  • SQL Server Import and Export Wizard Error: “Index was outside the bounds of the array” via 32bit ODBC data source?
  • How to create an SQL table and and populate it with Excel spreadsheet data?
  • Unit Testing for VBScript, ASP Code and SQL Server 2000
  • Execute SQL Server 7 DTS on SQL Server 2008
  • Get DTS Step Description from TSQL?
  • 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.