megadarkfriend megadarkfriend - 6 months ago 11
SQL Question

Inserting data into SQL database with potentially duplicate data

I have two databases on different servers, which have tables called dbo.A. The data is both is largely the same, but I want to make sure both tables have the same data. I've been using the SQL server June 2016 to export data from one table to the other, but the error I get is:

Violation of PRIMARY KEY constraint ''. Cannot insert duplicate key in object A

The duplicate key value is 'Some text here'


I know I can delete the table and reinsert the rows, but that's cumbersome and pretty bad practice. What would be the best way for me to update the data in the second database?

Answer

Add the server as your linked server and use the following statement.

To add rows in TableA from say Serve B's Table A.

INSERT INTO dbo.A (Col1 , Col2 , Col3 , ....)
SELECT Col1 , Col2 , Col3 , ....
FROM [LinkedServerB].[DBName].[dbo].[A] A 
WHERE NOT EXISTS ( SELECT 1 FROM dbo.A 
                   WHERE A.PK_Column = PK_Column)

And then use the same query on Server B to add the rows from Server A

To add rows in TableA from say Serve B's Table A.

INSERT INTO dbo.A (Col1 , Col2 , Col3 , ....)
SELECT Col1 , Col2 , Col3 , ....
FROM [LinkedServerA].[DBName].[dbo].[A] A 
WHERE NOT EXISTS ( SELECT 1 FROM dbo.A 
                   WHERE A.PK_Column = PK_Column)