Match two tables.
Question posted by: rsrinivasan
(Familiar Sight)
on
July 4th, 2008 09:06 AM
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,
Would you like to answer this question?
Sign up for a free account, or Login (if you're already a member).
|
|
July 4th, 2008 10:30 AM
# 2
|
Re: Match two tables.
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
|
|
July 4th, 2008 10:56 AM
# 3
|
Re: Match two tables.
Quote:
Originally Posted by 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
|
Both table structure are same and has same data. I just want to check whether any of the data is changed or modified.
|
|
July 9th, 2008 04:37 PM
# 4
|
Re: Match two tables.
Try doing this:
Code: ( text )
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
Not the answer you were looking for? Post your question . . .
182,266 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).
|
|
|
Top Microsoft SQL Server Contributors
|
