Difference between two tables
-
Hi, I have two identical tables in 2 seperate databases. 1 is the "central" DB and its contents can change. Basically I want to write a poece of code to run periodically to ensure the contents of table 2 are up to date with that of table 1. Id imagine this is a relativaly common task so I was wondering are there any smart ways of doing it or any existing code knocking around? Thanks in advance,
-
Hi, I have two identical tables in 2 seperate databases. 1 is the "central" DB and its contents can change. Basically I want to write a poece of code to run periodically to ensure the contents of table 2 are up to date with that of table 1. Id imagine this is a relativaly common task so I was wondering are there any smart ways of doing it or any existing code knocking around? Thanks in advance,
You could try something like this:
SELECT a.pk, b.pk
FROM FirstDatabase.dbo.TableName AS a
FULL OUTER JOIN SecondDatabase.dbo.TableName AS b WHERE a.pk = b.pk
WHERE a.pk IS NULL
OR b.pk IS NULLpk= primary key, if you have a compound key then you will need all the columns that make up the primary key. The results of the query should (I haven't tested it) return any rows that exist in one database, but don't in the other. If you want to return all rows that have differences then you might want to add to the WHERE clause:OR a.column1 <> b.column1
OR a.column2 <> b.column2...and so on for each of the columns. ColinMackay.net Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
-
You could try something like this:
SELECT a.pk, b.pk
FROM FirstDatabase.dbo.TableName AS a
FULL OUTER JOIN SecondDatabase.dbo.TableName AS b WHERE a.pk = b.pk
WHERE a.pk IS NULL
OR b.pk IS NULLpk= primary key, if you have a compound key then you will need all the columns that make up the primary key. The results of the query should (I haven't tested it) return any rows that exist in one database, but don't in the other. If you want to return all rows that have differences then you might want to add to the WHERE clause:OR a.column1 <> b.column1
OR a.column2 <> b.column2...and so on for each of the columns. ColinMackay.net Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
Sorry I should have mentioned the two DB's are actually running on 2 different servers
-
Sorry I should have mentioned the two DB's are actually running on 2 different servers
That's okay. The naming convention extends to servers. Just add in the server name like this: ServerName.DatabaseName.SchemaName.TableName SchemaName is
dbo, unless you've set it up otherwise. You will also have to link the two servers together. You might find this useful: MSDN: Configuring Linked Servers[^] ColinMackay.net Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?