SCD type 1 with merge table data

mpedrosompedroso Registered User

Hi

I have to create an ETL for my diodes using SCD type 1. Soon, I thought of carrying out the following steps:

  1. 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.
  2. 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.

Table target:
CREATE TABLE target (id IDENTITY, a INT, b INT, c INT);

Table Source:
CREATE TABLE source (a INT, b INT, c INT);

Command merge:
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?

Comments

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file