SQL Server job to check 2 databases and act based on result?

Question

I need to check 2 tables on similar databases on different servers, if they contain the same information (or size, hash, whichever is the best way to check if they're the same) then one of them should have its information deleted, on a scheduled job every day.

Is this possible using only the Jobs interface from within SQL Server Management Studio?

Answer 1

As you are on SQL 2008, you could use the MERGE command to do the check (assuming you are still talking about the linked server table comparison from your other question here).

You would setup a job that runs under an account with the correct permissions to read the data from the linked server as the source for your MERGE command and then delete the entries in your target table that match it:

MERGE DBNAME.SCHEMANAME.TABLENAME t --this is your target (the local table)
using (SELECT * from SERVERB.DBNAME.SCHEMANAME.TABLENAME) s --this is your source (the linked server)
on (t.uniquefield = s.uniquefield) --join condition

when matched then delete -- when matched, then delete!
;