Wsrtjtyk

I'm building a layer over an application that needs to catch the changes happens to the data and update another system with these changes (SIF) and I faced a problem with a specific table, the application truncates the table, and insert a new set of records every time the data reconciled.
In order to solve this problem, I used a shadow table and Merged the records from the original table, and as I found that I might use the same method with other tables in the future, I created a generic SP that reads the structure of the tow tables and constructs a merge statement then runs it and I'm sharing the SP in the first answer, hope someone makes use of it, any comment or question is welcomed.
The SP works as long as the two tables are identical and the change tracking is working beautifully.

1- Creating the SP

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



CREATE Procedure [Compare2Tables](

    @DestSchema as NVarchar(255) ,

    @DestTable as NVarchar(255),

    @SrcSchema as NVARCHAR(255) ,

    @srcTable as NVARCHAR(255) ,

    @AdditionalCondition as NVARCHAR(MAX) 

    )

AS

BEGIN

DECLARE @JoiningFields as NVARCHAR(MAX)

DECLARE @MismatchingCondition as NVARCHAR(MAX)

DECLARE @UpdateOtherFields as NVARCHAR(MAX)

DECLARE @InsertDestFields as NVARCHAR(MAX)

DECLARE @InsertSrcFilds as NVARCHAR(MAX)

DECLARE @TheSQL as NVARCHAR(MAX)

DECLARE @CurrentColumn as NVARCHAR(255)

DECLARE @CurrentConstraint as NVARCHAR(255)

DECLARE @tablespecs TABLE (

TABLE_SCHEMA nvarchar(255) ,

TABLE_NAME  nvarchar(255) ,

COLUMN_NAME nvarchar(255) ,

CONSTRAINT_NAME nvarchar(255) 

)



insert into @tablespecs SELECT DISTINCT T.TABLE_SCHEMA , T.TABLE_NAME , T.COLUMN_NAME ,CONSTRAINT_NAME 

FROM INFORMATION_SCHEMA.COLUMNS t

LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K ON T.TABLE_NAME = K.TABLE_NAME AND T.TABLE_SCHEMA = K.TABLE_SCHEMA AND T.COLUMN_NAME = K.COLUMN_NAME

WHERE T.TABLE_NAME = @DestTable

AND T.TABLE_SCHEMA = @DestSchema



set @JoiningFields = '  '

set @MismatchingCondition = '  '

set @UpdateOtherFields  = ' '

set @InsertDestFields = ' '

set @InsertSrcFilds = ' '



while exists (select * from @tablespecs) 

    Begin

        set @CurrentColumn = (Select top 1 Column_name from @tablespecs)

        --select @CurrentColumn

        Set @CurrentConstraint = (Select CONSTRAINT_NAME FROM @tablespecs WHERE COLUMN_NAME = @CurrentColumn)

        if not @CurrentConstraint is null 

            set @JoiningFields = @JoiningFields + ' D.' + @CurrentColumn + '=S.' + @CurrentColumn + ' AND '

        ELSE

            begin

            SET @MismatchingCondition = @MismatchingCondition + ' ISNULL(D.' + @CurrentColumn + ',0) <> ISNULL(S.' + @CurrentColumn + ',0) OR '

            SET @updateOtherFields = @updateOtherFields + 'D.' +@CurrentColumn  + ' = S.' + @CurrentColumn + ','

            end

            set @InsertDestFields = @InsertDestFields + @CurrentColumn + ','

            set @InsertSrcFilds = @InsertSrcFilds + 'S.' + @CurrentColumn + ',';



        delete from @tablespecs where Column_Name = @CurrentColumn

    End

    SET @JoiningFields = SUBSTRING(@JoiningFields , 1 , len(@JoiningFields) - 4)

    SET @MismatchingCondition = SUBSTRING(@MismatchingCondition , 1 , len(@MismatchingCondition) - 3)

    SET @UpdateOtherFields = SUBSTRING(@UpdateOtherFields , 1 , len(@updateOtherFields) - 1)

    SET @InsertDestFields = SUBSTRING(@InsertDestFields , 1 , len(@InsertDestFields) - 1)

    SET @InsertSrcFilds = SUBSTRING(@InsertSrcFilds , 1 , len(@InsertSrcFilds) - 1)



--select @JoiningFields JoiningFields , @UpdateOtherFields UpdateOtherFields , @MismatchingCondition MismatchingCondition , @InsertDestFields InsertDestFields , @InsertSrcFilds InsertSrcFilds



set @TheSQL = 'MERGE INTO ' + @DestSchema + '.' + @DestTable + ' AS D using (SELECT * FROM ' + @SrcSchema+'.'+ @SrcTable + ' ' + @AdditionalCondition + ') AS S ON ' + @JoiningFields + ' WHEN MATCHED AND (' + @MismatchingCondition + ') 

THEN UPDATE SET ' + @updateOtherFields + ' 

WHEN NOT MATCHED BY TARGET THEN

INSERT (' + @InsertDestFields + ') 

VALUES (' + @InsertSrcFilds + ')

WHEN NOT MATCHED BY SOURCE THEN

DELETE;'



EXECUTE sp_executesql @TheSQL



END

2- Now see the implementation

--Create theSource table

CREATE TABLE TheSource

(

TheID INT PRIMARY KEY,

TheName VARCHAR(100),

TheCost MONEY,

ProductionYear VARCHAR(4)

) 

GO



--Fill some records in TheSource

INSERT INTO TheSource

VALUES

(1, 'Word', 10.00,'2018'),

(2, 'Access', 20.00,'2018'),

(3, 'Excel', 30.00,'2017'),

(4, 'PowerPoint', 40.00,'2017')

GO



--Create Destination table

CREATE TABLE TheDest

(

TheID INT PRIMARY KEY,

TheName VARCHAR(100),

TheCost MONEY,

ProductionYear VARCHAR(4)

) 

GO

--The Dest table is left with no records on purpose

SELECT * FROM TheSource

SELECT * FROM TheDest

GO



--The folloing syntax will fill only products of 2017

execute [Compare2Tables]   'dbo','TheDest','dbo', 'TheSource','Where ProductionYear = 2017'

SELECT * FROM TheDest



-- Syncronizing all records regardless of the year

execute [Compare2Tables]   'dbo','TheDest','dbo', 'TheSource',' '

SELECT * FROM TheDest



--Updating one row in the source, then sync

update TheSource set TheCost = 33.00 where TheName = 'Access'

execute [Compare2Tables]   'dbo','TheDest','dbo', 'TheSource',' '

SELECT * FROM TheDest



-- updating all records in the source, then sync

update TheSource set TheCost = TheCost * 0.75

execute [Compare2Tables]   'dbo','TheDest','dbo', 'TheSource',' '

SELECT * FROM TheDest