Framing out a stored procedure

Transact-SQL
1

Hi Folks.

Can someone with a bit more knowledge and experience here help validate something for me? I'm working with two tables, a source and a target, the target table is regularly updated with new values from the source. However, there are instances where a row or more will be deleted from the source table but that is not reflected in the updates to the target table. The target table provides reports that are currently being validated against the source table and the reports are inaccurate due to the deleted rows not being accounted for in the target table.

My solution is to compare the value of a field called RcID in the target table, to all RcID values found in the source table that occur within a timestamp of nine to two days prior (this procedure will be scheduled for a Monday for the previous week of Sunday through Saturday). If a given RcID value exists in the target table but not in the source table, I want to update a field called 'deleted bin' in the target table for that given RcID value. Where I'm getting confused (because I'm not well verse in SQL) is in framing out the statement... here's what I have come up with so far... any advice is greatly appreciated.

CREATE PROCEDURE CompareAndUpdateDeletedBin
AS
BEGIN
    -- Update DeletedBin field in target table for RcIDs that exist in target but not in source
    UPDATE target.recvCart
    SET DeletedBin = 'x'
    WHERE RcID IN (
        SELECT source.RcID
        FROM source.recvCart target
        LEFT JOIN target.recvCart source ON source.RcID = target.RcID
        WHERE source.RcID IS NULL);
3 

CREATE PROCEDURE CompareAndUpdateDeletedBin
AS
BEGIN
SET NOCOUNT ON;
-- Update DeletedBin field in target table for RcIDs that exist in target but not in source
UPDATE target
SET DeletedBin = 'x'
FROM target.recvCart target
LEFT OUTER JOIN source.recvCart source ON source.RcID = target.RcID
WHERE source.RcID IS NULL;
END
[/quote]
4

Maybe I am missing something. If you are updating TARGET on addition to SOURCE, why not update TARGET on deletion to SOURCE as well, rather than chasing it later? I am assuming SOURCE must contain things TARGET does not or that TARGET is also updated from other sources, or I would question why the reports are not simply run against SOURCE.

5

Valid question. The reason is we are combining multiple sources into a single target, so the reporting is being built around the target. Basically we are pulling data into an Azure SQL DB from multiple in-house SQL DB's and a hosted MySQL DB. We have runtime integrations for the dataset and Data Factory pipeline runs for updates. The problem we are having is when a record is deleted from the source, it is not reflected in the target since we have no way to compare. So we are working on a solution to reconcile the target against the source.