Newbie Howto:Import, update, delete legacy data to SQLServer db

Hi,
I have a SQL db which I need to update weekly with data from a legacy
db but only where the legacy data has changed. The reason for this is
I have to add a couple of fields to the SQL db and they need to remain
linked to thier respective records.
I'd need to delete from the SQLdb any records absent in the legacy db
but presant in the SQL db, insert any records presant in the legacy db
but absent from the SQLdb and update any records in the SQLdb that
have changed in the legacy db.
Can anyone give me an idea on how an SQL statement to do this would
look. Or any other way of doing this?
Thanks in advance[posted and mailed, please reply in news]

Hitcher (steveChambers66@.hotmail.com) writes:
> I have a SQL db which I need to update weekly with data from a legacy
> db but only where the legacy data has changed. The reason for this is
> I have to add a couple of fields to the SQL db and they need to remain
> linked to thier respective records.
> I'd need to delete from the SQLdb any records absent in the legacy db
> but presant in the SQL db, insert any records presant in the legacy db
> but absent from the SQLdb and update any records in the SQLdb that
> have changed in the legacy db.
> Can anyone give me an idea on how an SQL statement to do this would
> look. Or any other way of doing this?

DELETE target
FROM target t
WHERE NOT EXISTS (SELECT *
FROM source s
WHERE t.keycol = s.keycol)

UPDATE target
SET col1 = s.col1,
col2 = s.col2,
...
FROM target t
JOIN source s ON t.keycol = s.keycol

INSERT target(...)
SELECT ...
FROM source s
WHERE NOT EXISTS (SELECT *
FROM target t
WHERE t.keycol = s.keycol)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp