The idea of this code was to insert new rows into a table after determining which "load_id"(s) are not present.
MERGE INTO stg.my_table AS tgt
using stg.my_view AS src
ON tgt. load_id = src.load_id
WHEN NOT matched THEN
INSERT (id,
document_type,
state,
publication_date,
)
VALUES (src.id,
src.document_type,
src.state,
src.publication_date,
);
Is there a way to achieve the same result without having to use a MERGE statement? Isn't an INSERT enough?
CodePudding user response:
Using INSERT INTO
:
INSERT INTO stg.my_table -- column list here
SELECT src.*
FROM stg.my_view AS src
LEFT JOIN stg.my_table AS tgt
ON tgt.load_id = src.load_id
WHERE tgt.load_id IS NULL;
Also using EXISTS
INSERT INTO stg.my_table -- column list here
SELECT src.*
FROM stg.my_view AS src
WHERE NOT EXISTS (SELECT *
FROM stg.my_table AS tgt
WHERE tgt.load_id = src.load_id);
Extras: