Hello Experts,
I have a Source table and a Target table. Please consider the following tables as an example. Now I would like to generate dynamic SQL based on the ETL table. Before generating the dynamic SQL, I tried using the static code and I am only able to do UPDATE on a Business Key but not an INSERT for Type-2 tables. How can I do an UPSERT (UPDATE & INSERT) when a Business Key is matched which has two or more records in the source?
CREATE TABLE Src_Employees
(Emp_ID NUMBER,
Emp_Last_Name VARCHAR2(20 CHAR),
Emp_First_Name VARCHAR2(20 CHAR),
Emp_Address_1 VARCHAR2(30 CHAR),
Emp_Address_2 VARCHAR22(30 CHAR),
Emp_Address_3 VARCHAR(30 CHAR),
Emp_City VARCHAR2(30 CHAR),
Emp_State VARCHAR2(2 CHAR),
Efctv_Dt_Time TIMESTAMP,
Inactv_Dt_Time TIMESTAMP
);
INSERT INTO Src_Employees (Emp_Last_Name,
Emp_First_Name,
Emp_Address_1,
Emp_Address_2,
Emp_Address_3,
Emp_City,
Emp_State,
Efctv_Dt_Time,
Inactv_Dt_Time)
VALUES('John', 'Doe', '123 Ave', '', '', 'Dallas', 'TX', '2018-10-17 16:14:19.733', '2018-10-17 16:16:17.123');
INSERT INTO Src_Employees (Emp_Last_Name,
Emp_First_Name,
Emp_Address_1,
Emp_Address_2,
Emp_Address_3,
Emp_City,
Emp_State,
Efctv_Dt_Time,
Inactv_Dt_Time)
VALUES('John', 'Doe', '456 Ave', '', '', 'Dallas', 'TX', '2018-10-17 16:16:17.123', '9999-12-31 23:59:59.000');
CREATE TABLE Tgt_Employees
(Emp_ID NUMBER,
Emp_Last_Name VARCHAR2(20 CHAR),
Emp_First_Name VARCHAR2(20 CHAR),
Emp_Address_1 VARCHAR2(30 CHAR),
Emp_City VARCHAR2(30 CHAR),
Emp_State VARCHAR2(2 CHAR),
Efctv_Dt_Time TIMESTAMP,
Inactv_Dt_Time TIMESTAMP
);
INSERT INTO Tgt_Employees (Emp_Last_Name,
Emp_First_Name,
Emp_Address_1,
Emp_City,
Emp_State,
Efctv_Dt_Time,
Inactv_Dt_Time)
VALUES('John', 'Doe', '123 Ave', 'Dallas', 'TX', '2018-10-17 16:14:19.733', '9999-12-31 23:59:59.000');
CREATE TABLE ETL
(
SRC_TABLE VARCHAR(30),
SRC_COLUMN VARCHAR(30),
TGT_TABLE VARCHAR(30),
TGT_COLUMN VARCHAR(30),
COL_POS VARCHAR(3),
BK_IND VARCHAR(1)
)
INSERT INTO ETL VALUES ('Src_Employees', 'Emp_Last_Name', 'Tgt_Employees', 'Emp_Last_Name', 1, 'Y');
INSERT INTO ETL VALUES ('Src_Employees', 'Emp_First_Name', 'Tgt_Employees', 'Emp_First_Name', 2, 'Y');
INSERT INTO ETL VALUES ('Src_Employees', 'Emp_Address_1', 'Tgt_Employees', 'Emp_Address_1', 3, 'N');
INSERT INTO ETL VALUES ('Src_Employees', 'Emp_City', 'Tgt_Employees', 'Emp_City', 4, 'N');
INSERT INTO ETL VALUES ('Src_Employees', 'Emp_State', 'Tgt_Employees', 'Emp_State', 5, 'N');
INSERT INTO ETL VALUES ('Src_Employees', 'Efctv_Dt_Time', 'Tgt_Employees', 'Efctv_Dt_Time', 6, 'N');
INSERT INTO ETL VALUES ('Src_Employees', 'Inactv_Dt_Time', 'Tgt_Employees', 'Inactv_Dt_Time', 7, 'N');
MERGE INTO Tgt_Employees AS tab1
USING
(
SELECT *
FROM Src_Employees
) AS tab2
ON tab1.Emp_Last_Name = tab2.Emp_Last_Name
AND tab1.Emp_First_Name = tab2.Emp_First_Name
WHEN MATCHED AND tab1.Efctv_Dt_Time = tab2.Efctv_Dt_Time
THEN UPDATE SET
tab1.Emp_Address_1 = tab2.Emp_Address_1,
tab1.Emp_City = tab2.Emp_City,
tab1.Emp_State = tab2.Emp_State,
tab1.Efctv_Dt_Time = tab2.Efctv_Dt_Time,
tab1.Inactv_Dt_Time = tab2.Inactv_Dt_Time
WHEN NOT MATCHED
THEN
INSERT
VALUES
(tab2.Emp_Last_Name,
tab2.Emp_First_Name,
tab2.Emp_Address_1,
tab2.Emp_City,
tab2.Emp_State,
tab2.Efctv_Dt_Time,
tab2.Inactv_Dt_Time
);
Expected output is when I do MERGE I would like to see one UPDATE and one INSERT in the target table.
Thanks!
Message was edited by: Julaayi Added DML commands for source and target tables and the desired output.