Incremental load in T-SQL with recorded history

Question

Please help me, I need do a incremental process to my dimensions, to store history data too by T-SQL. I am trying use the MERGE statement, but it doesn't work, because this process deletes data that exists in the target but not in the source table.

Does someone have a suggestion ?

For exemple I have the source table: The source table is my STAGE,
Cod Descript State
AAA Desc1 MI
BBB Desc 2 TX
CCC Desc 3 MA
In the first load my dimension will be equal STAGE

However I can change the value in source table for exemple

AAA CHANGEDESCRIPTION Mi

So, I need update my dimension like this:
Cod Descript State
AAA Desc1 Mi before
AAA CHANGEDESCRIPTION MI actual
BBB Desc 2 TX actual
CCC Desc 3 MA actual
This is my DW and I need the information actual and all history

Answer 1

Try this. Column Aging is always "0" for current record and indicates change generation:

SELECT * INTO tbl_Target FROM (VALUES
('AAA','Desc1','MI',0),('BBB','Desc 2','TX',0),('CCC','Desc 3','MA',0)) as X(Cod, Descript, State, Aging);
GO
SELECT * INTO tbl_Staging FROM (VALUES ('AAA','Desc4','MI')) as X(Cod, Descript, State);
GO
UPDATE t SET Aging += 1
FROM tbl_Target as t
INNER JOIN tbl_Staging as s on t.Cod = s.Cod;
GO
INSERT INTO tbl_Target(Cod, Descript, State, Aging)
SELECT Cod, Descript, State, 0
FROM tbl_Staging;
GO
SELECT * FROM tbl_Target;

Please note that if you have records in staging table, which are "unchanged", you'll get false changes. If so, you have to filter them out in both queries.

Answer 2

I just commented the clause DELETE...tell me what do you think please

        MERGE DimTarget AS [Target] --— begin merge statements (merge statements end with a semi-colon)
        USING TableSource AS [Source]
        ON [Target].ID = [Source].ID AND [Target].[IsCurrentRow] = 1
        WHEN MATCHED AND --— record exists but values are different
        (
                [Target].Dscript <> [Source].Descript
            )
        THEN UPDATE SET --— update records (Type 1 means record values are overwritten)
              [Target].[IsCurrentRow] = 0
             -- , [Target].[ValidTo] = GETDATE()
        WHEN NOT MATCHED BY TARGET --— record does not exist
        THEN INSERT --— insert record
        (
            Descritp
        , [IsCurrentRow]
        )
        VALUES
        (
        Descript
        , 1
        )
       --WHEN NOT MATCHED BY SOURCE --— record exists in target but not source
       --THEN DELETE -- delete from target
        OUTPUT $action AS Action, [Source].* --— output results