comparing content of two tables

I have a table which I copied some time back as a back up.

In the original table some records are removed or changed.

The problem is that IDs of the two tables are different.

Is there a way to compare the records of each table and point out any record is changed or added or removed?

I would know how to write a program and compare the contents but I was wondering if there was a sql command to do that?

thanks

I am using ms sql.
goodkAsked:
Who is Participating?
 
awking00Commented:
The original question was looking for records that had been added, removed, or changed from one table to the next. Now it seems you want to determine records that have certain fields that are the same. These are two different problems. Perhaps you can provide some sample data that include the various possibilities and what you want to see as results.
0
 
lwadwellCommented:
There needs to be a 'key' column (or columns) to be able to join on to do a comparison.  Is there a 'natural' key within the table that can be used?  i.e. a column or group of columns that could be considered as unique/PK.

Otherwise ... if you exclude the ID columns ... a rudimentary set of MINUS would identify rows in 1 table that are not identical in the other ... you need to do this twice e.g.
SELECT col1, col2, col3 ... colx
FROM table1
MINUS
SELECT col1, col2, col3 ... colx
FROM table2

and

SELECT col1, col2, col3 ... colx
FROM table2
MINUS
SELECT col1, col2, col3 ... colx
FROM table1
0
 
awking00Commented:
I think the keyword in ms sql is EXCEPT rather than MINUS. Using the method that lwadwell provided will show records that were either added to or changed from one table to the next. Determining which is complicated by the fact that you may not have a primary key.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
goodkAuthor Commented:
Wow, that works fine.  but this does not tell me that if in my table I have a duplicate record

I want to say that if two particular fields are repeated than the record is duplicated.
0
 
lwadwellCommented:
Duplicate based on what?  All columns or only a subset?  Normally to determine that you would do something like:

SELECT col1, col2, col3
FROM table
GROUP BY col1, col2, col3
HAVING count(*) > 1
0
 
goodkAuthor Commented:
super help; really appreciate it - thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.