SQL Data clone using Merge keyword

Question

Hi Folks,

I want to insert records from one Db to another Db but i facing one issue.

I want to check DeptId AND TaxId not matched then Insert,but before insert I check DeptId is exsist in "Department" table if not exsist then insert records from "Database1" to "MainDb" after then check I check TexId is exsist in "tblTax_M" table if not exsist then insert records from "Database1" to "MainDb" after then Insert records in "Department_Md" but get DeptId and taxId from MainDb.

Second thing How can I insert like below

INSERT   VALUES (S)

is it possible?

 

DECLARE @SqlQuery VARCHAR(MAX)
DECLARE @DbName VARCHAR(30)= 'Database1'
DECLARE @MainDB VARCHAR(30) = 'MainDb'

SET @SqlQuery = 
			'MERGE ' + QUOTENAME(@MainDB) + '.[dbo].[Department_Md] AS T
			 USING (SELECT * FROM ' + QUOTENAME(@DbName) + '.[dbo].[Department_Md]) AS S
			 	ON T.DeptId = S.DeptId AND T.TaxId = S.TaxId
			 WHEN NOT MATCHED BY TARGET THEN 
				IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(@MainDB) + '.[dbo].[Department] WHERE DeptId = S.DeptId)
				BEGIN 
				  INSERT 
					([DeptId],[BranchId],[DeptCode],[DeptName],[POS],[Is_asItem],[ChargeTyp],[ChargeAmt],[DeductChk],[POSITION],[FreqPOS],
					 [ChkExtra],[TaxFlg],[ChkCheckCash],[CheckCashType],[CheckCashAmt],[ChkApplyAge],[ApplyAgeDesc],[GroupTyp],[ImagePath],
					 [UserId],[IsDeleted],[ProfitMargin],[CreatedDate],[IsGASPump],[TaxApplyIn],[IsNotApplyInItem],[Remarks],[IsPOS],[IsSetting],
					 [IsNotDisplay],[IsNotDisplayInventory],[IsNotMerchandise],[DeptTypeId],[IsEBT],[Code],[IsMoneyOrder],[IsCashBack],
					 [DeptCategoryId],[minAmt],[maxAmt],[isAllowFS],[isNegative],[isAllowFQ],[isAllowSD],[prohibitDisc],[isBL1],[isBL2],
					 [isSNPromptReqd],[sysid],[IsFeeDeposite],[IsECommerceCategory])
				  VALUES
					(S.DeptId,S.BranchId,S.DeptCode,S.DeptName,S.POS,S.Is_asItem,S.ChargeTyp,S.ChargeAmt,S.DeductChk,S.POSITION,S.FreqPOS,
					 S.ChkExtra,S.TaxFlg,S.ChkCheckCash,S.CheckCashType,S.CheckCashAmt,S.ChkApplyAge,S.ApplyAgeDesc,S.GroupTyp,S.ImagePath,
					 S.UserId,S.IsDeleted,S.ProfitMargin,S.CreatedDate,S.IsGASPump,S.TaxApplyIn,S.IsNotApplyInItem,S.Remarks,S.IsPOS,S.IsSetting,
					 S.IsNotDisplay,S.IsNotDisplayInventory,S.IsNotMerchandise,S.DeptTypeId,S.IsEBT,S.Code,S.IsMoneyOrder,S.IsCashBack,
					 S.DeptCategoryId,S.minAmt,S.maxAmt,S.isAllowFS,S.isNegative,S.isAllowFQ,S.isAllowSD,S.prohibitDisc,S.isBL1,S.isBL2,
					 S.isSNPromptReqd,S.sysid,S.IsFeeDeposite,S.IsECommerceCategory);
				END
				IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(@MainDB) + '.[dbo].[tblTax_M] WHERE TaxId = S.TaxId)
				BEGIN 
				  INSERT
					([TaxId],[BranchId],[TAXNAME],[PERCENTAGE],[Type],[CreatedDate],[Amount],[CreatedBy],[IsDeleted],[TaxType],[IsIncludedInPrice]
					,[indicator],[isPromptExemption],[pctStartAmt],[Sysid])
				  VALUES
					(ISNULL((SELECT MAX(TaxId) FROM ' + QUOTENAME(@MainDB) + '.[dbo].[tblTax_M]), 0) + 1,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)
				END
				
			 	
			 	INSERT 
			 		([DeptId],[TaxId],[BranchId],[Amount],[TaxSysId])
			 	VALUES 
			 		(SELECT DeptId FROM ' + QUOTENAME(@MainDB) + '.[dbo].[Department] 
					Where DeptName IN (SELECT  TOP 1 DeptName FROM ' + QUOTENAME(@DbName) + '.[dbo].[Department] Where DeptId IN (S.DeptId))
					,SELECT TaxId FROM ' + QUOTENAME(@MainDB) + '.[dbo].[tblTax_M] 
					 Where TAXNAME IN (SELECT TOP 1 TAXNAME FROM ' + QUOTENAME(@DbName) + '.[dbo].[tblTax_M] Where TaxId IN (S.TaxId)),
					S.BranchId,S.Amount,S.TaxSysId);'
						
	PRINT 'Department (Department_Md) data insert successfully'
	EXEC sp_executesql @SqlQuery