Querying a SQL table and only transferring updated rows to a different database

Question

I have a database table which constantly gets updated. I am looking to query only the changes/additions that have been made on rows with a specific attribute in a column. e.g. get the rows which have been changed/added, the 'description' column of which is "xyz". My end goal is to copy these rows to another table in another database. Is this even possible? The reason for not just querying and overwriting the rows in the other database is to avoid inefficiency.

What I have tried so far?

• I am able to select query on the table to get the rows but it gives me all the rows, not the ones that have been changed or recently added. If i add these rows to the table in the other database, the only option I have is to overwrite the rows.

• Log table logs the changes in a table but I can't put additional filters in SQL which tells me which of these changes are associated with 'description' column as 'xyz'.

Answer 1

Write your update statements to make use of OUTPUT to capture the before and after values and log them to a table of your choice.

Here is a really simple example update example that uses output to store the RowID, before and after values for the ActivityType column:

DECLARE @MyTableVar table (  
    SummaryBefore nvarchar(max),  
    SummaryAfter nvarchar(max),
    RowID int
    );  

update DBA.dbo.dtest set ActivityType = 3
OUTPUT deleted.ActivityType,   
       inserted.ActivityType,
       inserted.RowID
    INTO @MyTableVar  

    select * From @MyTableVar

Answer 2

You can do it two ways

1. Have new date fields/columns like update_time and/or create_time(Can be defaulted if needed). These fields will indicate the status of the record. You need to save your previous_run_time and then your select query will look for records with update_time/create_time greater than previous_run_time, and then you can move these records to the new DB.

2. Have CDC turned on the source table, which is available by default in SQL server and then move only those records that have been impacted.