I want to create a SQL script that would compare 2 of the same fields in two different tables. These tables may be in two different servers. I want to use this script to see if one field gets updated in one table/server, it is also updated in the other table/server. Any ideas to approach this?
1 Answers
First thing you need to be sure of is that your servers are linked, otherwise you won't easily be able to compare the two. If the servers are linked, and the tables are identical you can use an EXCEPT query to identify the changes e.g.
select * from [server1].[db].[schema].[table]
except
select * from [server2].[db].[schema].[table]
This query will return all rows from the table in server1 that don't appear in server2 from here you can either wrap this in a count or insert/update the missing/changed rows from one table to another
Identifying whether the rows have changed or been inserted will rely on using a primary key, with that you can join one table to another and identify what needs updating using a query like so:
select *
from [server1].[db].[schema].[table] t1
inner join [server2].[db].[schema].[table] t2 on t1.id = t2.id
where ( t1.col1 <> t2.col1 or t1.col2 <> t2.col2 ... )
Another way of tracking changes is to use a DML trigger and have this propagate changes from one table to another.
I was working on a SQL Server auditing tool that uses these principles, have a look through the code if you like its not 100% working https://github.com/simtbak/panko/blob/main/archive/Panko%20v003.sql
Related