CODEWITHSUNDEEP
sqlsql-server
Adam_231
Adam_231

Reputation: 71

SQL - Query for duplicate records against different database servers

We have two separate SQL servers, each hosting the same table. Essentially one of these is a 'slave' and the other a 'master'. The master calls the slave every 10 mins and pulls the records from the slave to the master then deletes the ones on the slave.

However, we now have a scenario whereby (due to a coding bug within the application), there are some records which exist on the Master AND the Slave, which is preventing the transfer of records from completing as the process bombs out with an exception error around duplicate entries.

What I'm trying to do is come up with a query which will display all records which are duplicates (ie exist on both servers) in order to then purge these from the Slave.

I've used the Registered Servers option in Management Studio to add the two servers to a single sql query. I've tested this by identifying a single record which exists on both DBs and using a Select statement I can find it, logic as follows;

SELECT [Reserved]
FROM   [sctracking].[dbo].[scTracking]
WHERE [Reserved] = 'WHATEVER_THE_VALUE_IS'

This returns two rows, something like

Servername - Reserved
Server1    - WHATEVER_THE_VALUE_IS
Server2    - WHATEVER_THE_VALUE_IS

What I was then 'trying' to do was use a count value to run across both DB's, something like;

SELECT [Reserved]
       , COUNT(1) as CNT
FROM [sctracking].[dbo].[scTracking]
GROUP BY [Reserved]

However the above logic clearly doesn't work.

Any help anyone could give me here would be very greatly appreciated.

Upvotes: 0

Views: 245

Answers (1)

SQL_M
SQL_M

Reputation: 2475

I am not sure I understand you correctly, but assuming I do, when you link the servers, it's as easy as this:

SELECT ColA, ColB FROM ServerNameA.DatabaseA.dbo.TableA AS A
WHERE EXIST 
( SELECT * FROM ServerNameB.DatabaseB.dbo.TableB AS B
  WHERE A.ColA = B.ColA )

Upvotes: 1

Related Questions