comparing data between two databases

[tirf]

Hi everyone,
I have this task to compete it within a week, and need some idea how to start it.
we have 2 databases with identical schema objects, and 90% data. we are in the process of merging the two databases. So I want to compare the data in the tables (column by column) and find what data is different for analysis.
the problem is there are 320 tables to compare.
so can somebody suggest a way to accomplish this?

Thanks

 

[johnherman]

Do you have a data profiling tool? If not, you will need to write custom profiling scripts.

Stuff like, select "table-A column-name", distinct (table-A.column_name) from database-A.table-A union select "table-B column-name", distinct (table-A.column_name) from database-B.table-A --> this query will examine the domain of values and differences between table-A in each database.

==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[gmmastros]

This is something easy to get wrong. As such, I would suggest you look at a pre-built tool to do this for you. I have never used this tool but I have heard good things about it.

http://www.red-gate.com/products/sql-development/sql-data-compare/

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tirf]

Thanks guys for the responses.
So I am using the visual studio 2012 Data compare tool to do my data compare. And it works out perfect for me.
Now the next step is how to merge the data. I am generating all the DML scripts (Insert or update), but couldn't run this scripts because of the constraints.
can you give an advice what the best way will be to merge data.

I am thinking of 2 options

1. disable all constraints and execute the DMLs and enable them
2. find out the hierarchy (Parent-child relation) and execute the scripts based on it.

what say you professionals

Thanks