SQL Data Clone Using Merge statement

The syntax for conditional actions in T-SQL within a MERGE statement involves using the WHEN MATCHED or WHEN NOT MATCHED clauses along with conditions. However, in your provided code, the conditions are not explicitly written within the WHEN MATCHED clause. I'll adjust the code to include the conditions and update the Type column accordingly:

DECLARE @SqlQuery VARCHAR(MAX)

SET @SqlQuery =
    'MERGE ' + QUOTENAME(@MainDB) + '.[dbo].[tblTax_M] AS T
    USING (SELECT * FROM ' + QUOTENAME(@DbName) + '.[dbo].[tblTax_M] ) AS S
        ON T.TAXNAME = S.TAXNAME
    WHEN MATCHED THEN
        IF S.Type = 1
        BEGIN
            EXEC SP_InsertTye S.Type;
            UPDATE SET T.Type = 0; -- Assuming Type should be updated to 0 when S.Type is 1
        END
        ELSE
            UPDATE SET T.Type = S.Type * 12;

    WHEN NOT MATCHED BY TARGET THEN
        INSERT
            ([TaxId],[BranchId],[TAXNAME],[PERCENTAGE],[Type],[CreatedDate],[Amount],[CreatedBy],[IsDeleted],[TaxType],[IsIncludedInPrice]
            ,[indicator],[isPromptExemption],[pctStartAmt],[Sysid])
        VALUES
            (S.TaxId,S.BranchId,S.TAXNAME,S.PERCENTAGE,S.Type,S.CreatedDate,S.Amount,S.CreatedBy,S.IsDeleted,S.TaxType,S.IsIncludedInPrice
            ,S.indicator,S.isPromptExemption,S.pctStartAmt,S.Sysid);'

PRINT 'Tax(tblTax_M) data insert successfully'
EXEC sp_executesql @SqlQuery

In this adjusted code, I added a condition within the WHEN MATCHED clause to check if S.Type is equal to 1. If true, it calls the stored procedure and updates the Type column in the target table to 0. If false, it updates the Type column to S.Type * 12. Adjust the conditions and actions according to your specific logic and requirements.

Please  try after changing like this.

DECLARE @SqlQuery VARCHAR(MAX)
DECLARE @Type INT; -- Assuming @Type is declared somewhere in your code

SET @SqlQuery =
    'MERGE ' + QUOTENAME(@MainDB) + '.[dbo].[tblTax_M] AS T
    USING (SELECT * FROM ' + QUOTENAME(@DbName) + '.[dbo].[tblTax_M]) AS S
    ON T.TAXNAME = S.TAXNAME
    WHEN MATCHED THEN
        UPDATE SET
            T.Type = CASE WHEN S.Type = 1 THEN 0 ELSE @Type * 12 END
        WHEN NOT MATCHED BY TARGET THEN
        INSERT
            ([TaxId], [BranchId], [TAXNAME], [PERCENTAGE], [Type], [CreatedDate], [Amount], [CreatedBy], [IsDeleted], [TaxType], [IsIncludedInPrice],
            [indicator], [isPromptExemption], [pctStartAmt], [Sysid])
        VALUES
            (S.TaxId, S.BranchId, S.TAXNAME, S.PERCENTAGE, S.Type, S.CreatedDate, S.Amount, S.CreatedBy, S.IsDeleted, S.TaxType, S.IsIncludedInPrice,
            S.indicator, S.isPromptExemption, S.pctStartAmt, S.Sysid);';

PRINT 'Tax(tblTax_M) data insert successfully'
EXEC sp_executesql @SqlQuery;