在不断删除并重新插入其记录的表上使用更改跟踪 [英] Using change tracking on a table that has his records constantly deleted and re-inserted

问题描述

我正在一个应用程序上构建一个层，该层需要捕获数据发生的更改并使用这些更改(SIF)更新另一个系统，我遇到了特定表的问题，该应用程序将表截断并插入每次核对数据时都会有一组新的记录. 为了解决此问题，我使用了影子表并合并了原始表中的记录，并且由于我发现以后可能会与其他表使用相同的方法，因此我创建了一个通用SP来读取该表的结构.拖曳表并构造一个合并语句，然后运行它，我将在第一个答案中共享SP，希望有人使用它，欢迎任何评论或问题. 只要两个表相同，并且更改跟踪运行良好，SP便可以正常工作.

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

这篇关于在不断删除并重新插入其记录的表上使用更改跟踪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！