insert if not present in snowflake-CodePudding

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:

Both patterns above are syntax that are executed as