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

Question

I do not know where to start or how it will work, and I hope someone has an idea or a proven method.

To show an example of what I'm trying to do, I created a stored procedure that UPDATES or INSERTs new records in the local table, retrieving records from the 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 and not get only the new records that were inserted, or the old updated records?

I do not want to constantly receive all records, add new records or update records.

Is it possible?

Here is the stored procedure that I created as an example to update 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 it possible?

Thanks everyone!

Answer 1

Usually we add two date / time fields to the source table, for example Source.LastModifiedOn and Source.CreatedOn.

Then, when the task runs to update the target table, you can say that I get all the rows of Source.LastModifiedOn and Source.CreatedOn since the last run of the task and performing your row-based updates / inserts.

Of course, you must be sure that Source.LastModifiedOn and Source.CreatedOn are configured correctly.

Answer 2

I would use the OUTPUT clause with the $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 

Answer 3

You can write a trigger on your local table that will fire when it is updated or pasted. As part of the trigger logic, you must insert these updated or recently inserted records into the temporary table. This is the easiest way to save change records to a local table.

Answer 4

This is basically an RDotLee answer option , but it is an alternative approach that I sometimes use when I don't need the actual date / time when the records were created:

I just add one bit field called modified with a default value of 1.
If a new row is inserted in the table, modified automatically set to 1.
If the existing row is updated, I have to make sure that the modified parameter is set to 1.

Thus, the task needs to search only all lines with modified = 1 .
There is no need to keep track of the date / time the work was last completed.

When the task is completed successfully, the last thing it does is “reset” the modified field in all lines:

 update TheTable set modified = 0 

IMO, this approach works less when you just need to have the lines changed since the job was last run, but not when they were changed.

But it has the same drawback as the RDotLee approach "LastModifiedOn / CreatedOn" - you need to make sure that each update in the table really sets the modified column to 1, so you can only use it when you are in control of all the code that is written to the table.

Answer 5

Timestamp

TimeStamp increments and inserts or updates.

On the Master do this TimeStamp and on the Slave do it binary (8)

 select [timeStampSlave].* from [timeStampSlave] join [timeStampMaster] on [timeStampSlave].[ID] = [timeStampMaster].ID and [timeStampSlave].[timeStamp] < [timeStampMaster].[timestamp] 

For server queries, you can use the following syntax

 [MasterSever].[test].[dbo].[timeStampMaster]