SQL Interview Question | Technical Implementation of SCD1 slowly changing dimension Type 1
In this vedio, we will learn how to implement SCD1 technically in sql server .
We will learn 3 ways of doing it , below is the code used in vedio
-- source
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EMP_source' AND TABLE_SCHEMA = 'dbo')
DROP TABLE [dbo].[EMP_source];
GO
create table EMP_source
(
id int,
name varchar(10),
loc varchar(10)
)
-- target
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EMP_target' AND TABLE_SCHEMA = 'dbo')
DROP TABLE [dbo].[EMP_target];
GO
create table EMP_target
(
id int,
name varchar(10),
loc varchar(10),
dw_insert_timestamp datetime,
dw_update_timestamp datetime,
)
-- how to identify which records are eligible for insert and which are eligible for update
insert into EMP_target values (1,'sanjay','Pune',current_timestamp,CURRENT_TIMESTAMP)
insert into EMP_source values (1,'sanjay','Hyderabad')
insert into EMP_source values (2,'biswa','Pune')
insert into EMP_source values (3,'aniket','Pune')
select * from EMP_source
select * from EMP_target
--merge into
-----------------
MERGE INTO EMP_target AS T
USING EMP_source AS S
ON T.id = S.id
WHEN MATCHED THEN
UPDATE SET
T.name = S.name,
T.loc = S.loc,
T.dw_update_timestamp = GETDATE()
WHEN NOT MATCHED THEN
INSERT (id, name, loc, dw_insert_timestamp, dw_update_timestamp)
VALUES (S.id, S.name, S.loc, GETDATE(), GETDATE());
--- update and insert using left outer join
UPDATE T
SET T.name = S.name,
T.loc = S.loc,
T.dw_update_timestamp = GETDATE()
FROM EMP_target AS T
LEFT JOIN EMP_source AS S
ON T.id = S.id
WHERE S.id IS NOT NULL;
INSERT INTO EMP_target (id, name, loc, dw_insert_timestamp, dw_update_timestamp)
SELECT S.id, S.name, S.loc, GETDATE(), GETDATE()
FROM EMP_source AS S
LEFT JOIN EMP_target AS T
ON S.id = T.id
WHERE T.id IS NULL;
----------------------------------------- supporting queries for left join
select *
from EMP_source s
left outer join EMP_target t
on s.id=t.id -- all records
select *
from EMP_source s
left outer join EMP_target t
on s.id=t.id
where t.id is not null -- update records
select *
from EMP_source s
left outer join EMP_target t
on s.id=t.id
where t.id is null -- brnd new (insert) records
------------------------ update and insert using inner join
UPDATE T
SET T.name = S.name,
T.loc = S.loc,
T.dw_update_timestamp = GETDATE()
FROM EMP_target AS T
INNER JOIN EMP_source AS S
ON T.id = S.id;
INSERT INTO EMP_target (id, name, loc, dw_insert_timestamp, dw_update_timestamp)
SELECT S.id, S.name, S.loc, GETDATE(), GETDATE()
FROM EMP_source AS S
WHERE S.id NOT IN (SELECT id FROM EMP_target);