SQL Data Clone Using Merge statement

Question

Hi Folks,

When the data is a match then I want to call SP and Insert the record in a different table.

How can I set and check conditionally like the case when or If else with a different table Insert or Update?

Look at the below code for a better understanding

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
					SET @Type = 0
				END
				EXEC SP_InsertTye S.Type
				Update T.Type = @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

Prasad Raveendran · Answer

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.

Jithu Thomas · Answer

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;