Comparing data and integrity between two tables

Question

Comparing data and integrity between two tables

JarrodR

SSC Enthusiast

Points: 108
More actions
June 4, 2008 at 1:37 am

#191305

Hi everyone,
I have recently converted my DTS packages to SSIS and deployed them to the new server. I have the 2000 and 2005 server running concurrently, all that is left for me to do is compare the the tables generated by the DTS and SSIS packages to see if they are the same.
How do I go about comparing the tables, which are from two different servers using SQL server 2005?
Thank you inadvance:)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

Well, start by comparing the schemas... it should be easy enough to do manually. Then do a row count. If you need deeper analysis then you'll need to write custom SQL based off of your needs.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Hmm - might be one of those times to dust off the new EXCEPT clause.

Select col1, col2, col3, col4

from TableA

EXCEPT

Select col1, col2, col3, col4

from TableB

would return rows from table A that don't match up in table B (meaning one or more columns from the SELECT clause has been changed, or the row doesn't exist).

You coul also get into LEFT OUTER or FULL OUTER if you want a different layout.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 3

I like Matt's solution, had forgotten the EXCEPT clause.

you can also get a tool, Red Gate, ApexSQL, a few others make "compare" tools that will compare the schema and/or data of the tables.

Depending on what the packages do, you have to decide what to compare. Row counts might work, calculating some checksum on tables, or comparing fields. It gets cumbersome comparing fields without a tool, but you can write scripts, with big WHERE clauses that can do it.

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 4

One more, if the data is supposed to be the same, you could export using BCP to char format and "diff" the text files. DIFF would work, or a source control program might diff two files. I think VS does this.

JarrodR SSC Enthusiast Points: 108 More actions · Answer 5

Thanks guys, I've found this code

select 'Table1' as tblName, * from

(select * from Table1

except

select * from Table2) x

union all

select 'Table2' as tblName, * from

(select * Table2 except select *

from Table1) x

...When All data in tables are equal the result is ZERO

Mike Levan SSChampion Points: 14201 More actions · Answer 6

How can i update table A from Table B when i found records from the EXCEPT cluse, ie; want to update table A with table B where i found records are not matching.

VK-924161 Newbie Points: 7 More actions · Answer 7

hi Mike,

I have a similar problem. Did someone reply back to you?

VK