We help IT Professionals succeed at work.

SQL - compare tables and create another tables that are different

I would like to compare two tables A and B  with the identical schema.  
I try to compare if anything change and produce a table with the changes.

I use a query to query multiple tables and insert all the results into Table A for Day 1
I use the same query to query multiple tables and insert all the results into Table B on Day  2.
Then I would like to see what has changed in Table A by comparing with Table B and I would like to save the result into another Table same schema as Table A and Table B.

How do I do that?
Comment
Watch Question

BRONZE EXPERT
Top Expert 2010

Commented:
What counts as a change?

I think it would be useful for you to post a few sample rows from each table, and the output you expect given that input.

Author

Commented:
Any new row in Table B
Any change in value in any columns of a record in Table B

The column can be string, integer, float, datetime type.
BRONZE EXPERT
Top Expert 2010
Commented:
You'll need to adjust for actual table column names, as well as for the actual number of columns...


SELECT b.col1, col2, col3, ..., colN
INTO TableC
FROM TableA a RIGHT JOIN
    TableB b ON a.col1 = b.col1 AND a.col2 = b.col2 AND 
        a.col3 = b.col3 AND ... AND a.colN = b.colN
WHERE a.col1 IS NULL

SELECT * FROM TableC

Open in new window

Author

Commented:
Mattewpatrick,

Thanks.  Is there a way to create a generic comparison that can work on any two tables that have the same schema. Or how will I start to write up such a comparison.
BRONZE EXPERT
Top Expert 2010

Commented:
That may be possible by utilizing some of the system tables, but it is beyond my skill level.
BRONZE EXPERT
Top Expert 2012
Commented:
Is there a way to create a generic comparison that can work on any two tables that have the same schema. Or how will I start to write up such a comparison.
You need to use something like Red-Gate's SQL Compare:
http://www.red-gate.com/products/sql-development/sql-compare/

Author

Commented:
I went across  the EXCEPT command

SELECT * FROM TableB EXCEPT SELECT * FROM TableA

That should returns any distinct values from the left query (Table B)

This is seems to what I need.  Has any one use this command?
Or is there any side effect that I should watch out? I like to make sure that this is the right command
Senior DBA
SILVER EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
MS provides a free utility program called tablediff.exe that can do that for you.

It's a line command utility, so getting it to work the first time is some work (and possibly some frustation, depending on how familiar you are with line command tools), but it's pretty good once you get used to it given that it's free and can be run programatically.

Author

Commented:
Thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.