• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 462
  • Last Modified:

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?
0
tommym121
Asked:
tommym121
3 Solutions
 
Patrick MatthewsCommented:
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.
0
 
tommym121Author 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.
0
 
Patrick MatthewsCommented:
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

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tommym121Author 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.
0
 
Patrick MatthewsCommented:
That may be possible by utilizing some of the system tables, but it is beyond my skill level.
0
 
Anthony PerkinsCommented:
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/
0
 
tommym121Author 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
0
 
Scott PletcherSenior DBACommented:
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.
0
 
tommym121Author Commented:
Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now