SQL Import to update existing records?

Question

I've got a table in a database that contains costs for items that gets updated monthly. To update these costs, we have someone export the table, do some magic in excel, and then import the table back to the database. We're running MSSQL 2005 and using the built in SQL Management Studio. The problem is that when importing back into the table, we have to delete all the records before we import or else we'll get errors. The ideal situation would be for the import to recognize the primary keys and then update the records instead of trying to create a second record with a duplicate key- halting the import. The best illustration of the behavior we're trying to get can be found at http://sqlmanager.net/en/products/mssql/dataimport/documentation/hs2180.html the update or insert example. Is something like this possible with the built in tools or do we have to get third party software to make it happen?

Solution

In SQL Server 2008 you could put the data into a new table and use the MERGE statement with your existing table as the target, and your new table as your source.

Instead, you might prefer to put it into a new table and use that as your source with an old method (using two steps unfortunately):

UPDATE t SET col1 = s.col1, col2 = s.col2
FROM targetTable t
   JOIN
   newTable s
    ON s.id = t.id ;

INSERT targetTable (columnlist)
SELECT s.col1, s.col2, ...
FROM sourceTable s
   LEFT JOIN
   targetTable t
    ON s.id = t.id 
WHERE t.id IS NULL;

Bear in mind you may need to turn on IDENTITY_INSERT on your target table if you're trying to insert into an identity column as well.

Comments

[mikato]

I've been searching around on this as well and the answers are disappointingly complex. Another option I read is to remove the primary key constraint on your tables, just keeping it as unique constraint on those fields. Then after the Import and Export Wizard runs, reapply the primary key constraint.