Comparison of data in two different databases

sukhoi35
sukhoi35 used Ask the Experts™
on
Hello Experts,
Currently I am in the process of comparing a large number of tables between two databases. The source db is Sybase and the destination to which it was migrated to is SQL Server. One simple but a tedious way is to save the output as text files and compare it with each other using file comparison, which really is not efficient when the rows are several hundred thousands. Hence, would like to know if there is an easy way out.

Thanks in Advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
There is no efficient way, as long as you are unable to find restrictions what to check for. E.g. if you can use a primary key, checking existence is easy. Or if there is a modification timestamp, you can compare that.
Other than that, you would have to compare complete rows.

If you manage to have a Sybase connection from MSSQL, you can use a linked server definition and the compare in SQL directly.
If you can't do that, but can connect to both DBs from a PC, you would have to write up e.g. a PowerShell script performing a sophisticated row-by-row compare.
You can also re-import those tables into a second DB in MSSQL, and the check with SQL.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> One simple but a tedious way is to save the output as text files and compare it with each other using file comparison, which really is not efficient when the rows are several hundred thousands.

Yes, this will work provided you do some manual work in the middle.
There are few third party tools which can do this with certain limitations(I would say prerequisites) like Schema and Table structure to be similar to work like the below ones..

http://www.dbbalance.com/database_comparison.htm

Author

Commented:
Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial