Very simple problem I think: Compare 2 tables

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

General SQL Server Forums

New to SQL Server Programming

Very simple problem I think: Compare 2 tables

Author

Topic

munkifisht
Starting Member

1 Post

Posted - 2009-07-17 : 20:43:51

I am not a total newb to SQL, but may as well be, the little I used to know I have forgotten years ago, so any help here would be great. Ok, so I have two sets of data. The first is an existing list of names. Associated with each name is the date the name was first added and a true/false value. The name data is from a list of data that is updated on a website regularly. On the website list many new names are added every few days, and one or two may be deleted.

What I want to do is use SQL to compare the two sets of data and add any names are a in the 'new' table but not the 'existing' table. I want to insert and new records into the 'existing' table, while removing any records that are in the 'existing' table but not in the 'new'. Also, if records are being deleted because they are not in the 'new' list, I want to move them to another table (lets call it 'dump') so they can be checked before they are removed, as some of the name spellings might be changed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-18 : 01:25:27

[code]
--insert into existing table new records
insert into existing
select n.columns ...
from new n
left join existing e
on e.name=n.name
where e.name is null

--insert to dump obsolete records thats not in new
insert into dump
select e.columns...
from existing e
left join new n
on n.name=e.name
where n.name is null

--delete obsolete records from existing
delete e
from existing e
left join new n
on n.name=e.name
where n.name is null
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources