comparing two tables data - 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

General SQL Server Forums

New to SQL Server Programming

comparing two tables data

Author

Topic

anjali66
Starting Member

23 Posts

Posted - 2011-01-05 : 12:03:50

Hello Everyone,

I have two tables. I need to compare the data of those two tables and if the data in both the tables is same then I need to discard one table. Is there any way, i can compare the data between the tables rather than just visually looking at the data. I mean any scripts or any tool that I can write/use to compare the data between two tables.

Thanks in advance.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-05 : 12:10:39

you can compare the data in table via

select *
from t1
full outer join t2
on t1.col1 = t2.col1
and t1.col2 = t2.col2
....

makes a difference if the coluns are nullable

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2011-01-05 : 12:11:40

RedGate database comparison tool will tell you if there are any difference between two tables - and then you could drop the table manually if no differences - would that do?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-05 : 12:12:48

well at least one of the table should have all its column in the other one? if this is the case then you can simple use the Inner join over all those columns and to use the delete command ... e.g.

Delete T2
From TableName T1
Inner Join tableName T2
on (T1.Col1=T2.Col1 and T1.Col2=T2.Col2 and ... T1.ColN=T2.ColN)

other wise explain further with an example!

Cheers
MIK

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-05 : 12:15:13

SELECT Count(*)
FROM
(select * from table1
union
select * from table2
) a
The count should = the number of rows in table1 , which should obviously = the rows in table2

Jim

Everyday I learn something that somebody else already knew

Kristen
Test

22859 Posts

Posted - 2011-01-05 : 12:51:49

full outer join t2
on t1.col1 = t2.col1
and t1.col2 = t2.col2
....

needs further work to accommodate NULL values (as nigel said in his post)

on (T1.Col1=T2.Col1 and T1.Col2=T2.Col2 and ... T1.ColN=T2.ColN)

ignores all rows that have a column with a NULL value (but it will indeed delete rows where the columns are identical and ALL are NOT NULL

SELECT Count(*)
FROM
(select * from table1
union
select * from table2
) a

will give you grief if the total width of the columns is large (well, I think it will!, because I assume it has the same limitations as sorting in order to establish which are DISTINCT ...)

I also think it may fail if the number of rows in table1 is greater than table 2, but all rows in table 2 are identical to the rows in table 1 (but a check of the COUNT(*) for both tables would fix that?)

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-05 : 12:57:56

agree @Kristen: in case of Null Values use isnull() for all columns, as required. :) Yes it would be All or Not All case @" I need to compare the data of those two tables and if the data in both the tables is same then I need to discard one table." since it has not been mentioned that both tables are having identical/same number of columns, hence only joined the ones that are matching in both ..

cheers!

Kristen
Test

22859 Posts

Posted - 2011-01-05 : 13:05:04

"in case of Null Values use isnull() for all columns, as required"

I wouldn't - what you are going to IsNull them too that is guaranteed not to be in the data stream?

You have to go to the arseache of

(A.COL1 = B.COL1 OR (A.COL1 IS NULL AND B.COL2 IS NULL))

and you probably want a binary collation after the "COL1 = COL2" if the column is char and case insensitive so that you pick up anything which is only different in CASE.

RedGate compare will also pick up if the type of the columns is different. Not sure that matters, but I suppose you might get a false-positive if comparing columns if one is INT and the other SMALLINT or somesuch (but I can't think of one offhand ...).

I suppose VARCHAR and NVARCHAR might accidental match when not the same because of Collation Differences (but using a binary collation should fix that, but VARCHAR and Nvarchar have their own set of foibles

)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources