Table Comparison - 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

SQL Server Development (2000)

Table Comparison

Author

Topic

cutever
Starting Member

32 Posts

Posted - 2002-05-30 : 22:14:17

Do have any simple way to compare the following two tables and then return the records that have been modified?

I have two tables Member_Old (8 fields) and Member_New (10 fields)

Table: Member_Old

ID MCode JoinDate BranchID StateID Status Active Email
---------------------------------------------------------------
1 M111 12/15/2001 1 6 1 Y Null
2 M222 03/04/2001 1 2 1 N Null
3 M333 05/01/2001 5 2 1 Y Null
4 M444 07/03/2001 3 4 1 Y Null
5 M555 02/25/2001 2 2 0 Y Null
6 M666 10/18/2001 8 9 0 Y Null

Table: Member_New

ID MCode JoinDate BID BName SID SName Status Active Email
-------------------------------------------------------------------
1 M111 12/15/2001 1 KL 2 MAL 1 Y Null
2 M222 03/04/2001 1 KL 2 MAL 1 N aa@hotmail.com
4 M400 07/03/2001 3 Penang 2 MAL 1 Y Null
5 M555 02/25/2001 2 Selangor 2 MAL 0 Y Null
6 M666 10/18/2001 8 OCRoad 3 SIN 0 N Null
7 M777 11/20/2001 3 Melaka 2 MAL 1 Y Null
8 M888 08/30/2001 1 KL 2 MAL 1 Y ttt@hotmail.com

Member Record that been modified:

2 - Email,
3 - Member Leave,
4 - MCode,
7 – New Record,
8 – New Record

In order to remain the existing (unchanged) record, only update the changed record, and delete the record, which was not located in Member_New, so, I would like to make comparison before update. However, I don’t want to compare one by one using EXIST/ANY because the table may have more than 18 fields and I will update it frequently.

Please provide some guidance for me. Thank You!

Ver

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-05-30 : 22:59:03

quote:

Do have any simple way to compare the following two tables and then return the records that have been modified?

Well now that all depends by what you mean as "simple"

This post is the same [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12618[/url]

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 05/30/2002 22:59:56

DavidD
Yak Posting Veteran

73 Posts

Posted - 2002-05-30 : 23:08:28

Can't think of an easy way to check except comparing all fields, but for future reference how about putting a trigger on Member_new, it could write all changed or new records to a new table to be used as you see fit.

eg

Create trigger trg_MemberNew on Member_New for update, insert
as
insert into Member_Changes ([column list])
select [i.column list] from inserted i, Member_New m
where i.id = m.id

Regards
DD

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-05-30 : 23:11:02

as David has so eloquently put it....the link shows you how to generate some SQL to compare all columns of your tables...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources