我试图通过插入etl进程来完成以下表更新:
- 添加不存在的新记录
- 更新所有有更新的记录
- 保留所有未更改的记录
在表中,当记录被更新时,我有一个unique_id和一个updated_date字段(或者可以作为它第一次创建的日期)。下面是两个表的示例代码,我希望第三个表的输出在插入语句之后:
BEGIN TRANSACTION;
/* Create a table called NAMES */
CREATE TABLE NAMES(Id integer PRIMARY KEY, Name text, updated_date DATE);
CREATE TABLE NEW_NAMES(Id integer PRIMARY KEY, Name text, updated_date DATE);
CREATE TABLE FINAL_TABLE_DESIRED(Id integer PRIMARY KEY, Name text, updated_date DATE);
/* Create few records in this table */
INSERT INTO NAMES VALUES(1,'Tom', '2021-08-09');
INSERT INTO NAMES VALUES(2,'Lucy', '2021-08-07');
INSERT INTO NAMES VALUES(3,'Frank', '2021-08-07');
INSERT INTO NAMES VALUES(4,'Jane', '2021-08-07');
INSERT INTO NAMES VALUES(5,'Robert', '2021-08-07');
INSERT INTO NEW_NAMES VALUES(1,'Tom John', '2021-08-17');
INSERT INTO NEW_NAMES VALUES(70,'Lollah', '2021-08-07');
INSERT INTO FINAL_TABLE_DESIRED VALUES(2,'Lucy', '2021-08-07');
INSERT INTO FINAL_TABLE_DESIRED VALUES(3,'Frank', '2021-08-07');
INSERT INTO FINAL_TABLE_DESIRED VALUES(4,'Jane', '2021-08-07');
INSERT INTO FINAL_TABLE_DESIRED VALUES(5,'Robert', '2021-08-07');
INSERT INTO FINAL_TABLE_DESIRED VALUES(1,'Tom John', '2021-08-17');
INSERT INTO FINAL_TABLE_DESIRED VALUES(70,'Lollah', '2021-08-17');
COMMIT;
/* Display all the records from the table */
SELECT * FROM final_table_desired;
是否有比上面的ie和scd2更好的方法-如果有,为什么?
试试这个:
- 创建表FINAL_TABLE_DESIRED作为NAMES 的克隆
- 用FINAL_TABLE_DESIRED作为目标,NEW_NAMES作为源,写一个MERGE INTO语句
您可以使用MERGE语句来完成此操作
MERGE INTO NAMES
USING NEW_NAMES
ON NEW_NAMES.ID = NAMES.ID
WHEN MATCHED THEN UPDATE SET NAMES.NAME = NEW_NAMES.NAME, UPDATED_DATE = CURRENT_DATE()
WHEN NOT MATCHED THEN INSERT (ID, NAME, UPDATED_DATE) VALUES (NEW_NAMES.ID, NEW_NAMES.NAME, CURRENT_DATE());