SCD type 1 with merge table data
I have to create an ETL for my diodes using SCD type 1. Soon, I thought of carrying out the following steps:
- load the target table with the fields of the dimension + an id field, the id field being one of type identity. In this way, entering the data would create a substitute key for the record.
- Load the source table with the updated data (no substitute key) and perform a merge command to update the target table records so that the new records are inserted and a substitute key is automatically generated by the ID field of type identity. In case the record does not exist the specified fields would be updated.
CREATE TABLE target (id IDENTITY, a INT, b INT, c INT);
CREATE TABLE source (a INT, b INT, c INT);
MERGE INTO target t USING source s ON t.a = s.a
WHEN MATCHED THEN UPDATE SET a = s.a, b = s.b, c = s.c
WHEN NOT MATCHED THEN INSERT (a, b, c) VALUES (s.a, s.b, s.c);
Any solution to the problem?