table comparison - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

table comparison

Author

Topic

reddy463
Starting Member

3 Posts

Posted - 2014-11-20 : 09:57:40

I have a table contain 100 columns in the data base

I moved the data in the source table into multiple child tables.

Now i want to check weather the data in the source table and the data in the child tables are exactly same.

Here i have 10000 rows of data.

Please help me how to do this.

Thanks in advance.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-20 : 11:37:08

I use set operators for this. Assume A is the set of rows in the base table (the one with 100 colums). Let A' be a subset of A containing just the columns in one of the child tables. Let B be that child table. Then:

IF A'-B = B-A' = empty set, they are equal

In SQL:


IF NOT EXISTS(
    SELECT * from A_Prime
    EXCEPT
    SELECT * FROM B
)
AND NOT EXISTS (
    SELECT * from B 
    EXCEPT
    SELECT * FROM A_Prime
)
AND (SELECT COUNT(*) FROM A_Prime) = (SELECT COUNT(*) FROM B)
BEGIN
   PRINT 'Equal'
END

you need to compare the counts since Tables are multi-sets, or bags and can have duplicate rows.

reddy463
Starting Member

3 Posts

Posted - 2014-11-20 : 13:28:13

Thanks for your response but here i need to compare every value in the table

for example if you have table like

1 true
2 true
3 False
we need to check weather the value for 3 is false or not.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-20 : 13:36:21

that's what EXCEPT does. It compares every row in A with every row in B, column by column.

Please try my solution and post your query and results if you don't get what you want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources