how to compare 2 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

how to compare 2 tables

Author

Topic

naveenbalraj
Starting Member

18 Posts

Posted - 2009-08-03 : 23:44:18

Hi All,

I have a scenario, where there are 2 identical tables. I call one as master table and the other as temp table.

The user will first download the information from the master table (as excel), make edits to the columns and can as well add new rows anywhere in the excel sheet and then upload. Now this upload will be inserted into the temp table (I have completed this far).

Now, I need to compare the fields in the master table with temp table and if there are any changes in the field, need to update those columns in the master table and if there is any new row, that as well need to be inserted into the master table.

I tried a way refered in another example, that I found googling. Where I had primary key to both the tables, and compared the 2 table using union. But this will not work for a scenario, where the user can insert a new row anywhere. So the comparison might fail with id to id match.

Do you guys have any direction, on how to implement this.

Thanks,
NB

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-04 : 00:31:47

this will give you the rows that are diff plus new rows in the temp table but not in master


SELECT *
FROM   temp_table t
       LEFT JOIN master_table m ON t.pk = m.pk
WHERE  m.pk IS NULL
OR     t.somecol    <> m.somecol
OR     t.anothercol <> m.anothercol

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-04 : 05:00:50

IF the user can put a null value into the column then you will need to check for that as well. You might not have to bother with this if you aren't allowing NULLS in the column.


WHERE  m.pk IS NULL
OR t.somecol <> m.somecol
OR t.[somecol] IS NULL AND m.[somecol] IS NOT NULL
OR t.[somecol] IS NOT NULL AND m.[somecol] IS NULL

OR t.anothercol <> m.anothercol
OR t.[anothercol] IS NULL AND m.[anothercol] IS NOT NULL
OR t.[anothercol] IS NOT NULL AND m.[anothercol] IS NULL

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rhinton21
Starting Member

10 Posts

Posted - 2009-08-13 : 14:18:41

quote:
Originally posted by khtan

this will give you the rows that are diff plus new rows in the temp table but not in master

SELECT *
FROM temp_table t
LEFT JOIN master_table m ON t.pk = m.pk
WHERE m.pk IS NULL
OR t.somecol <> m.somecol
OR t.anothercol <> m.anothercol

KH
[spoiler]Time is always against us[/spoiler]

Hi guys, this post really helped me a lot.

I have a question though.

How do I go about inserting the results for the above query.

I need to insert the rows that are not in the master table. How do I go about doing this?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-13 : 16:02:53

quote:
Originally posted by rhinton21

quote:
Originally posted by khtan

this will give you the rows that are diff plus new rows in the temp table but not in master

INSERT master_table
SELECT t.*
FROM temp_table t
LEFT JOIN master_table m ON t.pk = m.pk
WHERE m.pk IS NULL
--OR t.somecol <> m.somecol
--OR t.anothercol <> m.anothercol

KH
[spoiler]Time is always against us[/spoiler]

Hi guys, this post really helped me a lot.

I have a question though.

How do I go about inserting the results for the above query.

I need to insert the rows that are not in the master table. How do I go about doing this?

you'll need to handle the updates with an UPDATE query, using the commented out part as your WHERE condition

naveenbalraj
Starting Member

18 Posts

Posted - 2009-08-30 : 16:58:50

Hi All,

Thanks for all the replies.

There is a scenario, where this query will not work.

Example:
The master table has 10 rows.
The temp table has 11 rows, but the extra row was added as 8th row. (As the query given as one of the solution, will do id to id match)

So, when we do id to id match..the result set will show all the rows from 8 - 11 as new row in the temp table. Is there any other way to take care of this condition?

Thanks for ur time,
Naveen

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-30 : 17:04:29

The row-insert in excel should not alter the id...then there is no problem.

No, you're never too old to Yak'n'Roll if you're too young to die.

naveenbalraj
Starting Member

18 Posts

Posted - 2009-08-30 : 17:37:26

Hi,

Thanks for the reply.

In both the master and temp table, I have the primary key as auto id. And the excel sheet does not have any primary ids.

The temp table is frequently deleted and inserted with a new records.

I just tried and the result is what I explained. It treats all the rows 8 and above as new rows. I could see why it is causing, because it is doing id to id comparison.

Also, when we do a row to row comparison, how do we find the field that is different, using the query suggested. Is it possible?

Thanks
Naveen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources