SQL Server: Copying table contents from one database to another

Question

I want to update a static table on my local development database with current values from our server (accessed on a different network/domain via VPN). Using the Data Import/Export wizard would be my method of choice, however I typically run into one of two issues:

1. I get primary key violation errors and the whole thing quits. This is because it's trying to insert rows that I already have.
2. If I set the "delete from target" option in the wizard, I get foreign key violation errors because there are rows in other tables that are referencing the values.

What I want is the correct set of options that means the Import/Export wizard will update rows that exist and insert rows that do not (based on primary key or by asking me which columns to use as the key).

How can I make this work? This is on SQL Server 2005 and 2008 (I'm sure it used to work okay on the SQL Server 2000 DTS wizard, too).

Answer 1

One option is to download the data to a new table, then use commands similar to the following to update the target:

update target set
    col1 = d.col1,
    col2 = d.col2
from downloaded d
inner join target t on d.pk = t.pk

insert into target (col1, col2, ...)
select (d.col1, d.col2, ...) from downloaded d
where d.pk not in (select pk from target)