June 5, 2009 at 4:49 am
I wish to compare records in two separate tables, and update one with field contents from the other. I also need to add new records to table 2, if they are not there already, and remove records, if they dont exist on table 1. I have worked out a way to iterate through 2 recordsets, performing updates, additions and deletes using asp, but i'm wondering is there a more efficient way to do it using t-sql.
Any pointers or advice most welcome.
June 5, 2009 at 9:01 am
Hi
I would start with the DELETion of removed data. After that use an UPDATE with OUTPUT to update the existing data and remember their ids. At last you can use an INSERT statement joined over the table filled by the UPDATE to insert new data.
DECLARE @source TABLE (Id INT, SomeInt INT)
DECLARE @dest TABLE (Id INT, SomeInt INT)
INSERT INTO @source
SELECT 1, 1
UNION ALL SELECT 2, 1
INSERT INTO @dest
SELECT 1, NULL
UNION ALL SELECT 3, NULL
DECLARE @done TABLE (Id INT)
---=================
-- Delete
DELETE d
FROM @dest d
LEFT JOIN @source s ON d.Id = s.Id
WHERE s.Id IS NULL
---=================
-- Update
UPDATE d SET
d.SomeInt = s.SomeInt
OUTPUT inserted.Id
INTO @done
FROM @dest d
JOIN @source s ON d.Id = s.Id
---=================
-- Insert
INSERT INTO @dest (
Id,
SomeInt
)
SELECT
s.Id,
s.SomeInt
FROM @source s
LEFT JOIN @done d ON s.Id = d.Id
WHERE d.Id IS NULL
SELECT * FROM @dest
Greets
Flo
June 6, 2009 at 5:27 am
Many thanks for the very detailed response. I wlll work on this over the next couple of days, and report back.
Cheers. 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply