July 9, 2008 at 11:07 pm
Hi, I was wondering if someone can help me with a problem I have. I have two identical tables (same columns), say
a source and destination. The destination has one extra column that is called 'flag'.
Basically, whenever a row is added, updated or deleted in the source table I want to reflect that into my destination table, PLUS set a flag accordingly.
So I probably need three separate queries for each. Say for delete I am going something like this to find records in destination that has been deleted:
SELECT * FROM DESTINATION T1
WHERE NOT EXISTS ( SELECT 1 FROM SOURCE T2 WHERE T1.key = T2.key)
I am stuck how do I update these flag for these rows in an update statement?
UPDATE ImportPlanProcessedTable
SET Processed = 3
WHERE .....
I am a noob at SQL so thanks in advance,
July 9, 2008 at 11:26 pm
maybe a trigger could help
"Keep Trying"
July 10, 2008 at 1:12 am
[Code]CREATE TRIGGER TABLE1_INSERT_TRIGGER
ON TABLE1
FOR INSERT
AS
SELECT *, 1
INTO TABLE2
FROM INSERTED
CREATE TRIGGER TABLE1_UPDATE_TRIGGER
ON TABLE1
FOR UPDATE
AS
UPDATE T2
SET T2.FLAG = 2
FROM TABLE2 T2
INNER JOIN TABLE1
ON TABLE2.ID = TABLE1.ID
CREATE TRIGGER TABLE1_DELETE_TRIGGER
ON TABLE1
FOR DELETE
AS
SELECT *, 3
INTO TABLE2
FROM DELETED[/Code]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply