SQL - Comparing Two Tables

Question

I'm really new to SQL, but I was given a task that I need to accomplish.

I have one table and from time to time I will get a new table. What I need to do is compare the new one with the old and see all differences (modified values, rows deleted or rows added). After getting all differences, I have to create a new table which will be an updated version of the first one.

After just one day reading about SQL I got as far as EXCEPT, so I know how to spot the differences (not sure if it's the best way though), but I don't know how to do all the process.

SELECT * FROM dbo.Price_List1
EXCEPT
SELECT * FROM dbo.Price_List2

Thank you very much in advance.

-1 because it's quite unclear what you're asking for: **1.** What do you mean by _"a new table"_? Do both tables (old and new) have exactly the same schema? **2.** Are you only interested in data differences (as opposed to schema differences)? **3.** What do you mean by _"updated version of the first one"_? Thanks for editing your question to clear up these points. — stakx - no longer contributing, Jun 24 '14 at 22:29
Btw.: Your `EXCEPT` query does not let you know about *all* differences. Assuming that both tables have exactly the same schema (not obvious due to the `*`; I suggest you get into the habit of spelling out all column names!), your query only tells you what records are exclusively in `dbo.Price_List1`. It won't tell you what records are exclusively in `dbo.Price_List2`; for that, you'd have to swap the two `SELECT` statements. — stakx - no longer contributing, Jun 24 '14 at 22:31
I'm sorry if I wasn't clear. Both tables have the same schema. Basically one column for ID, one for NAME and one for VALUE. What might change between tables is a change in the VALUE or maybe a row being added/removed. Instead of updating the first table with the new stuff, I would create a third table just to be safe. I'm really, really new so I'm sorry if I'm not being very clear. — Marcos, Jun 24 '14 at 22:38
See this : [SQL Query to return differences between two tables](http://stackoverflow.com/questions/2077807/sql-query-to-return-differences-between-two-tables) — Dangling Cruze, Jun 24 '14 at 22:47

score 0 · Answer 1 · answered Jun 25 '14 at 03:54

If schema of dbo.Price_List1 and dbo.Price_List2 was equal. you can use following query.

(   SELECT * FROM dbo.Price_List1
    EXCEPT
    SELECT * FROM dbo.Price_List2)  
UNION ALL
(   SELECT * FROM dbo.Price_List2
    EXCEPT
    SELECT * FROM dbo.Price_List1)

SQL - Comparing Two Tables

1 Answers1