SQL 合并以更新具有更改历史记录的表

一尘不染

SQL 合并以更新具有更改历史记录的表

sql

我的任务是在 SQL 中构建历史表。我已经构建了包含多个左连接的基表。需要将基表与另一个表进行比较，并且只更新已更改的特定列，在键不匹配的地方插入新行。

以前我使用过其他在 SCD 加载器中内置 GUI 风格的 ETL 工具，但我在 SQL Server 中没有这种奢侈。这里的merge语句可以处理这样的操作。我之前使用过该MERGE语句，但是在根据执行的操作处理标志和日期字段时，我变得有点卡住了。

这是基本表

KEY	CLIENT	QUANTITY	CONTRACT_NO	FC_COUNT	DELETE_FLAG	RECORD_UPDATED_DATE
345	A	1000	5015	1	N	31/12/9999
346	B	2000	9352	1	N	31/12/9999
347	C	3000	6903	1	N	31/12/9999
348	D	1000	7085	1	N	31/12/9999
349	E	1000	8488	1	N	31/12/9999
350	F	500	6254	1	N	31/12/9999

这是我计划合并的表

KEY	CLIENT	QUANTITY	CONTRACT_NO	FC_COUNT
345	A	1299	5015	1
346	B	2011	9352	1
351	Z	5987	5541	1

我正在寻找的结果是

KEY	CLIENT	QUANTITY	CONTRACT_NO	FC_COUNT	DELETE_FLAG	RECORD_UPDATED_DATE
345	A	1000	5015	1	N	06/07/2022
345	A	1299	5015	1	N	31/12/9999
346	B	2000	9352	1	N	06/07/2022
346	B	2011	9352	1	N	31/12/9999
347	C	3000	6903	1	Y	06/07/2022
348	D	1000	7085	1	Y	06/07/2022
349	E	1000	8488	1	Y	06/07/2022
350	F	500	6254	1	Y	06/07/2022
351	Z	5987	5541	1	N	31/12/9999

正如我们所看到的，我已经显示了更改，关闭了旧记录，标有日期和删除标志（如果它们丢失但之前存在），以及带有新键和数据的新行

这会是一个MERGE吗？关于如何执行这种操作的一些指导将是一个很大的帮助。我们有很多表需要保留更改历史记录，这将有助于向前发展。

代码外壳尝试

SELECT      
            MAIN_KEY,
            CLIENT,
            QUANTITY,
            CONTRACT_NO,
            1 AS FC_COUNT,
            NULL as DELETE_FLG_DD,
            GETDATE() as RECORD_UPDATED_DATE
INTO  #G1_DELTA
FROM
            [dwh].STG_DTL
MERGE [dwh].[PRJ1_DELTA] TARGET
USING #G1_DELTA SOURCE
ON   TARGET.MAIN_KEY                    = SOURCE.MAIN_KEY

WHEN MATCHED THEN INSERT 
    (
        MAIN_KEY,
        CLIENT,
        QUANTITY,
        CONTRACT_NO,
        FC_COUNT,
        DELETE_FLG_DD,
        RECORD_UPDATED_DATE
    )
VALUES
    (
        SOURCE.MAIN_KEY,
        SOURCE.CLIENT,
        SOURCE.QUANTITY,
        SOURCE.CONTRACT_NO,
        SOURCE.FC_COUNT,
        SOURCE.DELETE_FLG_DD,
        SOURCE.RECORD_UPDATED_DATE
        )

阅读 99

2022-07-22

共1个答案

一尘不染

如果您需要构建一个包含两个表中的更新信息的历史表，您首先需要从您的两个表中选择更新的信息。

需要应用于您的表的更改是：

“ tab1.[DELETE_FLAG]'Y' ”，只要它与 tab2 匹配就应该更新
“ tab1.[RECORD_UPDATED_DATE] ”，应该更新到当前日期
“ tab2.[DELETE_FLAG] ”，缺失，应初始化为N
“ tab2.[RECORD_UPDATED_DATE] ”，缺失，应初始化为您的随机日期9999-12-31。

完成这些更改后，您可以应用将UNION ALL两个表中的行放在一起。

然后，为了生成表，您可以使用 cte 选择输出结果集并使用INTO <table>选择后的子句来生成您的“历史”表。

WITH cte AS (
    SELECT tab1.[KEY],
           tab1.[CLIENT],
           tab1.[QUANTITY],
           tab1.[CONTRACT_NO],
           tab1.[FC_COUNT],
           CASE WHEN tab2.[KEY] IS NOT NULL
                THEN 'N'
                ELSE 'Y'
           END       AS [DELETE_FLAG],
           CAST(GETDATE() AS DATE) AS [RECORD_UPDATED_DATE]
    FROM      tab1
    LEFT JOIN tab2
           ON tab1.[KEY] = tab2.[KEY] 

    UNION ALL 

    SELECT *, 
           'N'          AS [DELETE_FLAG],
           '9999-12-31' AS [RECORD_UPDATED_DATE]
    FROM tab2
)
SELECT *
INTO history
FROM cte
ORDER BY [KEY];

在此处查看演示。

2022-07-22