Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to compare two tables on different databases Expand / Collapse
Author
Message
Posted Wednesday, June 10, 2009 10:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 07, 2009 1:28 AM
Points: 21, Visits: 44
Hi I have two databases on same server and both databases.These do databases are same (we can say replica) .There is a table name 'Transactions' on both databases.Now I have to create a job which runs on daily basis and check whether the data in Table 'Transaction' in both the databases is same or not (i.e to check whether table in both database is in sync) .How can I do this.Please suggest.
Post #732746
Posted Wednesday, June 10, 2009 11:06 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
jain_abhishek (6/10/2009)
Hi I have two databases on same server and both databases.These do databases are same (we can say replica) .There is a table name 'Transactions' on both databases.Now I have to create a job which runs on daily basis and check whether the data in Table 'Transaction' in both the databases is same or not (i.e to check whether table in both database is in sync) .How can I do this.Please suggest.



you can create a checksum for the values in the table (see checksum http://msdn.microsoft.com/en-us/library/ms189788(SQL.90).aspx and cheksum_agg (http://msdn.microsoft.com/en-us/library/ms188920(SQL.90).aspx) and then compare the checksums. You can do this on a per row level (you may want to do this via a linked server), or the whole table. The fewer checksums you compare the less accurate the result will be (i.e. if you calculate a checksum for the whole table you may encounter cases where the checksums match, but the tables are different. You should also consider calculating the checksums based on some inside knowledge (e.g. do the checksums on only a few columns if that is sufficient to determine a difference). The checksum calculation may be expensive, so do some initial tests (like row counts). If you need to compare all the data without checksums, use either linked servers or there are third party tools too.

Regards,
Andras




Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Post #732750
Posted Wednesday, June 10, 2009 11:18 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 26, 2012 10:06 AM
Points: 267, Visits: 385
use tablediff utility : C:\Program Files\Microsoft SQL Server\90\COM
give source and destination [servername , tablename , databasename]..
simple to use (consult BOL).
make sure that the table has unique/primary key (clustered index)

HTH


Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
Post #732755
Posted Wednesday, June 10, 2009 11:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 07, 2009 1:28 AM
Points: 21, Visits: 44
but both the tables are in different database.how can i compare
Post #732756
Posted Wednesday, June 10, 2009 11:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 07, 2009 1:28 AM
Points: 21, Visits: 44
One more thing If both table are not having same data then how will I make both tablles in sync ie same data in both table
Post #732757
Posted Thursday, June 11, 2009 12:07 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 26, 2012 10:06 AM
Points: 267, Visits: 385
its possible ........databases can be different ...
for your second query : its very much possible..
Very busy right now ....wil reply later ...till then do soem research


Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
Post #732768
Posted Thursday, June 11, 2009 5:37 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 03, 2012 5:49 AM
Points: 418, Visits: 365
Since databases are different, use fully qualified name of the databases i.e. prefix the table name with server IP.database.user.table

To comapre the results, u can use EXCEPT operator.
loke
select * from d1..table1
except
select * from d2..table2
Post #732941
Posted Friday, June 12, 2009 7:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 15, 2011 12:53 PM
Points: 243, Visits: 236
After you get the first compare done, you could add an update_dt field and have it default to the current getdate() value. Then, if there are dates since the last comparison, then those are the ones that are different.

There's also merge replication, once you got them in a spot where they are the same.

The EXCEPT is the way I would make that first compare, too.






Randy
Post #733787
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse