Implement scd-2 in oracle SQL

How can I implement scd 2 with SQL in below scenarios:

there is a table with below information:

id	name	product	start_date	end_date
1	A	P1	1-2-2020	8-8-8888
2	B	P2	2-2-2020	8-8-8888
3	C	P3	3-2-2020	8-8-8888
4	D	P1	2-3-2020	3-4-2020

I have a file :

id	name	product
1	A	P5
2	B	P6
3	C	P7

Expected table output:

id	name	product	start_date	end_date
1	A	P1	1-2-2020	4-4-2020
2	B	P2	2-2-2020	4-4-2020
3	C	P3	3-2-2020	4-4-2020
4	D	P1	2-3-2020	3-4-2020
1	A	P5	4-4-2020	8-8-8888
2	B	P6	4-4-2020	8-8-8888
3	C	P7	4-4-2020	8-8-8888

Edit: I have implemented this in datastage using change capture stage, but not sure how to do the same in SQL.

https://stackoverflow.com/questions/67260347/implement-scd-2-in-oracle-sql April 26, 2021 at 11:06AM