Update 2 or 3 fields post upsert

Help
1

Hi All,

we have stored procedure with 2 sql scripts, 1st one is merge statement and 2nd one is update 2 fields from the merge for the same target table.
Can anyone please help me how can i build the model in this situation, please find the stored procedure below,
CREATE PROCEDURE SAMPLE
AS
BEGIN

– First Insert/Update “TABLE_B” from Staging

MERGE TABLE_B AS TRG
USING ( SELECT
[Company],
[Desc],
[Claimable],
[Type],
[ValidFrom],
[ValidTill],
[Status],
[CreatedDate],
[ModifiedDate],
[BasedOn],
[BudgetLevel],
[CBP],
[Level],

[Code],
[Name]
FROM TABLE_A WITH(NOLOCK)) AS SRC
ON (
TRG.[Company] = SRC.[Company]
)
WHEN MATCHED THEN
UPDATE SET
TRG.[Company] = SRC.[Company],
TRG.[Desc] = SRC.[Desc],
TRG.[Claimable] = SRC.[Claimable],
TRG.[Type] = SRC.[Type],
TRG.[ValidFrom] = SRC.[ValidFrom],
TRG.[ValidTill] = SRC.[ValidTill],
TRG.[Status] = Case SRC.[Status] When ‘Y’ Then ‘Active’ When ‘N’ Then ‘Inactive’ End,
TRG.[ModifiedDate] = SRC.[ModifiedDate],
TRG.[BasedOn] = SRC.[BasedOn],
TRG.[BudgetLevel] = SRC.[BudgetLevel],
TRG.[CBP] = SRC.[CBP],
TRG.[Level] = SRC.[Level],
TRG.[Code]=SRC.[Code],
TRG.[Name] =SRC.[Name]
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[Company],
[Desc],
[Claimable],
[Type],
[ValidFrom],
[ValidTill],
[Status],
[CreatedDate],
[ModifiedDate],
[BasedOn],
[BudgetLevel],
[CBP],
[Level],

[Code],
[Name] )
VALUES (
SRC.[Company],
SRC.[Desc],
SRC.[Claimable],
SRC.[Type],
SRC.[ValidFrom],
SRC.[ValidTill],
Case SRC.[Status] When ‘Y’ Then ‘Active’ When ‘N’ Then ‘Inactive’ End,
SRC.[CreatedDate],
SRC.[ModifiedDate],
SRC.[BasedOn],
SRC.[BudgetLevel],
SRC.[CBP],
SRC.[Level],
SRC.[Code],
SRC.[Name] );

– First Update “TABLE_B” from Staging

;WITH cte AS
(
Select SE.Code, SE.ValidFrom, SE.ValidTill, ROW_NUMBER() OVER(PARTITION BY SE.Code ORDER BY SE.CreatedDate Desc) rNo
from TABLE_C SE with(nolock)
where EditType = 1 – Edit type 1 : If scheme validity extends.
)
Update S
set S.ValidFrom = CSED.ValidFrom, s.ValidTill = CSED.ValidTill
from TABLE_A S
Inner join cte CSED on CSED.Code = S.Code
where CSED.rNo = 1
end

2

in dbt , you have incremental strategy of merge .
new rows from source will be inserted ( by key)
and existing rows will be updated by definition .
if you want also to update after the merge , you can also use post hook .

{{ config(
materialized=‘incremental’,
unique_key=‘id’,
merge_strategy=‘merge’, exclude=[‘name’] – Exclude the ‘name’ column from the merge will not be updated
) }}