上有他的纪录不断被删除并重新插入一个表格利用变化跟踪(Using change tracking o

我在需要赶上变化恰好数据和更新这些变化（SIF）的另一个系统的应用建立一个层，我面临着一个问题，一个特定的表，应用截断表，并插入一个新的集的记录每一个数据核对的时间。为了解决这个问题，我用一个影子表，并从原来的表合并的记录，因为我发现，我可能会用在未来其他表同样的方法，我创建了一个通用的SP读取的结构拖表和构建一个MERGE语句然后运行它，我分享SP的第一个答案，希望有人利用它，任何意见或问题表示欢迎。因为这两个表是相同的，并且更改跟踪工作精美的SP可以作为长。

1-创建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-现在看落实

--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