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)

Answer 2

If you disable the FK constrains during the 2nd option - and resume them after finsih - it will work.

But if you are using identity to create pk that are involves in the FK - it will cause a problem, so it works only if the pk values remains the same.

Answer 3

I'm not sure you can do this in management studio. I have had some good experiences with RedGate SQL Data Compare in synchronising databases, but you do have to pay for it.

Answer 4

The SQL Server Database Publishing Wizard can export a set of sql insert scripts for the table that you are interested in. Just tell it to export just data and not schema. It'll also create the necessary drop statements.