Match two tables.

[rsrinivasan]

Hi all,

I am using SQLServer2000. I have two same tables. I want a query to check whether these two tables have same data. How can i check it?

Thanks,

[amitpatel66]

What is the table structure of both the tables? Do you want to compare column by column or by primary key?....

Try something like

SELECT * FROM table1 WHERE primary_column NOT IN (SELECT * FROM table2)
UNION
SELECT * FROM table2 WHERE primary_column NOT IN (SELECT * FROM table1)

THis will give you the records that are there in either of the tables but not in another one

[rsrinivasan]

What is the table structure of both the tables? Do you want to compare column by column or by primary key?....

Try something like

SELECT * FROM table1 WHERE primary_column NOT IN (SELECT * FROM table2)
UNION
SELECT * FROM table2 WHERE primary_column NOT IN (SELECT * FROM table1)

THis will give you the records that are there in either of the tables but not in another one

Both table structure are same and has same data. I just want to check whether any of the data is changed or modified.

[ck9663]

Try doing this:

select min(sourcetable) as sourcetable, field1, field2, field3, field4
from
(select 'table1' as sourcetable, field1, field2, field3, field4 from table1
union
select 'table2' as sourcetable, field1, field2, field3, field4 from table2) twotables
group by field1, field2, field3, field4 
having count(*) = 1
 

This will show you all rows that have at least a difference on one column. Remember, all fields that you want to compare should be inside the subqueries (union) and they should match (field1 goes to field1, etc). Just add all the fields on your table as necessary.

Happy coding.

-- CK