SQL Data clone using Merge keyword

When inserting into tblTax_M, the script uses ISNULL((SELECT MAX(TaxId) FROM ...), 0) + 1 to generate a new TaxId. This approach can be problematic if TaxId is supposed to be a unique identifier, especially in a concurrent environment where multiple transactions could lead to duplicate TaxId values.

Your script intends to insert into Department_Md only if certain conditions are met (existence of DeptId and TaxId in respective tables). However, you do not clearly separate these conditions for each insert operation, potentially leading to confusion and incorrect data insertion.

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

-- Insert DeptId from Database1 to MainDb if not exists
SET @SqlQuery = 'INSERT INTO ' + QUOTENAME(@MainDB) + '.[dbo].[Department] (/* column list */)
                 SELECT /* column list */ FROM ' + QUOTENAME(@DbName) + '.[dbo].[Department]
                 WHERE DeptId NOT IN (SELECT DeptId FROM ' + QUOTENAME(@MainDB) + '.[dbo].[Department])'
EXEC sp_executesql @SqlQuery

-- Insert TaxId from Database1 to MainDb if not exists
SET @SqlQuery = 'INSERT INTO ' + QUOTENAME(@MainDB) + '.[dbo].[tblTax_M] (/* column list */)
                 SELECT /* column list */ FROM ' + QUOTENAME(@DbName) + '.[dbo].[tblTax_M]
                 WHERE TaxId NOT IN (SELECT TaxId FROM ' + QUOTENAME(@MainDB) + '.[dbo].[tblTax_M])'
EXEC sp_executesql @SqlQuery

-- Insert into Department_Md
SET @SqlQuery = 'INSERT INTO ' + QUOTENAME(@MainDB) + '.[dbo].[Department_Md] (DeptId, TaxId, /* other columns */)
                 SELECT DeptId, TaxId, /* other columns */
                 FROM ' + QUOTENAME(@DbName) + '.[dbo].[Department_Md]
                 WHERE DeptId IN (SELECT DeptId FROM ' + QUOTENAME(@MainDB) + '.[dbo].[Department])
                 AND TaxId IN (SELECT TaxId FROM ' + QUOTENAME(@MainDB) + '.[dbo].[tblTax_M])'
EXEC sp_executesql @SqlQuery

PRINT 'Department (Department_Md) data insert successfully'

It looks like you're trying to perform a series of operations to insert records into various tables across different databases, and you want to make sure that certain conditions are met before inserting the data. Additionally, you are trying to use the VALUES clause with a subquery for the final INSERT.

Below is a revised version of your script

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 into Department table
            INSERT INTO ' + QUOTENAME(@MainDB) + '.[dbo].[Department]
                (DeptId, BranchId, DeptCode, DeptName, ... /* add other columns */)
            VALUES
                (S.DeptId, S.BranchId, S.DeptCode, S.DeptName, ... /* add other values */);
        END

        IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(@MainDB) + '.[dbo].[tblTax_M] WHERE TaxId = S.TaxId)
        BEGIN
            -- Insert into tblTax_M table
            INSERT INTO ' + QUOTENAME(@MainDB) + '.[dbo].[tblTax_M]
                (TaxId, BranchId, TAXNAME, PERCENTAGE, ... /* add other columns */)
            VALUES
                (ISNULL((SELECT MAX(TaxId) FROM ' + QUOTENAME(@MainDB) + '.[dbo].[tblTax_M]), 0) + 1,
                 S.BranchId, S.TAXNAME, S.PERCENTAGE, ... /* add other values */);
        END

        -- Insert into Department_Md table
        INSERT INTO ' + QUOTENAME(@MainDB) + '.[dbo].[Department_Md]
            (DeptId, TaxId, BranchId, Amount, TaxSysId)
        VALUES
            ((SELECT DeptId FROM ' + QUOTENAME(@MainDB) + '.[dbo].[Department]
              WHERE DeptName = (SELECT TOP 1 DeptName FROM ' + QUOTENAME(@DbName) + '.[dbo].[Department] WHERE DeptId = S.DeptId)),
             (SELECT TaxId FROM ' + QUOTENAME(@MainDB) + '.[dbo].[tblTax_M]
              WHERE TAXNAME = (SELECT TOP 1 TAXNAME FROM ' + QUOTENAME(@DbName) + '.[dbo].[tblTax_M] WHERE TaxId = S.TaxId)),
             S.BranchId, S.Amount, S.TaxSysId);
    ';

PRINT 'Department (Department_Md) data insert successfully'
EXEC sp_executesql @SqlQuery;

This revised script incorporates separate INSERT statements for each table and ensures that the conditions are checked before performing the insertions. The subqueries in the final INSERT statement retrieves the corresponding DeptId and TaxId from the MainDb based on the values in the source database.