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.

Go to Top of Page

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.


Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -