Compare two table? - 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 2000 Forums

Transact-SQL (2000)

Compare two table?

Author

Topic

dichanz
Starting Member

2 Posts

Posted - 2008-05-23 : 06:49:09

Hi All,
I have got two similar tables with 3 rows forming composite key - The structure is same.

I need to compare to see if the each records are same in both the table. like the way i did for non-composite key table as follows...

select * from TableName1
where <Pkey> not in (select <PKey> from TableName2)

In short, I need the extension of this for composite key

Cheers,
Dichan

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-23 : 06:56:05

[code]Select t1.*
from table1 t1 left join table2 t2
on t1.key1=t2.key1 and t1.key2 = t2.key2 ...
where t2.key1 is null and t2.key2 is null and ...[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-05-23 : 10:20:37

[code]
Select table1.*,
table2.*
from table1
full outer join table2
on table1.key1=table2.key1
and table1.key2 = table2.key2
and table1.key3 = table2.key3
where table1.key1 is null
or table2.key1 is null
[/code]

e4 d5 xd5 Nf6

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 10:59:37

[code]select cpk1,
cpk2,
cpk3,
min(tablename) as sourcetablename
from (
Select cpk1,
cpk2,
cpk3,
'table1' as tablename
from table1

union all

Select cpk1,
cpk2,
cpk3,
'table2'
from table2
) AS d
group by cpk1,
cpk2,
cpk3
having count(*) = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 11:03:14

[code]select t1.*
from TableName1 AS t1
where not exists (Select * from TableName2 as t2 where t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col3 = t2.col3)[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources