How to create a query that gets only data which has been updated in a table

Question

I have no idea where to start or how this will work and I am hoping someone has an idea or a proven method.

To show an example of what I am trying to do, I created a stored procedure which UPDATES or INSERT new records in a Local table by getting records from a link table. The Stored procedure runs as a Job in SQL Server to update and insert new records.

My question is: Is there a way to query the data in the Local Table so That initially I can get all the records but than get only new records that has been inserted or old records that are updated?

I do not want to continuously get all the records, just the new records added or the records updated.

Is this possible?

Here is the stored procedure I ave created as an example to updated the Local Phone data:

CREATE PROCEDURE sp_UPDATE_PHONE_RECORDS
AS 
BEGIN
MERGE dbo.PHONE_REC AS Target
USING (SELECT MEMBER_ID 
      ,HOME_PHONE = dbo.udf_StdPhoneFmt(HOME)
      ,CELL_PHONE = dbo.udf_StdPhoneFmt(CELL)
      ,WORK_PHONE = dbo.udf_StdPhoneFmt(WORK)

FROM PHONE WHERE MEMBER_ID IS NOT NULL) AS SOURCE

ON (Target.MEMBER_ID = SOURCE.MEMBER_ID)

WHEN MATCHED THEN

    UPDATE SET Target.HOME_PHONE = Source.HOME_PHONE,Target.CELL_PHONE = Source.CELL_PHONE,
    Target.WORK_PHONE = Source.WORK_PHONE

WHEN NOT MATCHED BY TARGET THEN
    INSERT (MEMBER_ID, HOME_PHONE, CELL_PHONE ,WORK_PHONE)

    VALUES (Source.MEMBER_ID, Source.HOME_PHONE, Source.CELL_PHONE, Source.WORK_PHONE);
END
GO

Is this possible?

Thanks everyone!

Answer 1

What we typically do is add two date / time fields to the source table, something like Source.LastModifiedOn and Source.CreatedOn.

Then when the job runs to update the target table you can say get me all the Source.LastModifiedOn and Source.CreatedOn rows since the last time the job ran and do your updates / inserts based on the rows.

Of course you will need to be sure the Source.LastModifiedOn and Source.CreatedOn are setup correctly.

Answer 2

I would use OUTPUT clause with $action column:

DECLARE @Target TABLE
(
    Id INT NOT NULL,
    Value VARCHAR(10) NULL
);
INSERT  @Target
VALUES  (1, 'A'),  (2, NULL), (3, NULL);    
DECLARE @Source TABLE
(
    Id INT NOT NULL,
    Value VARCHAR(10) NULL
);
INSERT  @Source
VALUES  (2, 'B'), (4, 'D'), (5, 'E');
DECLARE @AffectedRows TABLE
(
    MergeAction     NVARCHAR(10) NOT NULL,
    Old_Id          INT NULL,
    Old_Value       VARCHAR(10) NULL,
    New_Id          INT NULL,
    New_Value       VARCHAR(10) NULL
);

MERGE   @Target t
USING   @Source s ON t.Id = s.Id
WHEN MATCHED THEN
        UPDATE SET Value = s.Value
WHEN NOT MATCHED THEN
        INSERT (Id, Value) VALUES (s.Id, s.Value)
OUTPUT  $action, deleted.Id, deleted.Value, inserted.Id, inserted.Value 
        INTO @AffectedRows(MergeAction, Old_Id, Old_Value, New_Id, New_Value);

SELECT  * FROM  @Target;
SELECT  * FROM  @AffectedRows;

Results:

Id Value
-- -----
1  A
2  B    <-- updated row
3  NULL
4  D    <-- inserted row
5  E    <-- inserted row

MergeAction Old_Id      Old_Value  New_Id      New_Value
----------- ----------- ---------- ----------- ---------
INSERT      NULL        NULL       4           D
INSERT      NULL        NULL       5           E
UPDATE      2           NULL       2           B