Does WHEN clause in an insert all query when loading into multiple tables in Snowflake add a virtual field over each row and then load in bulk?

Question

How WHEN clause evaluate values of columns in order to insert only new values and skip existing ones when using the following query:

INSERT ALL
WHEN (SELECT COUNT(*) FROM DEST WHERE DEST.ID = NEW_ID) = 0 THEN
INSERT INTO DEST (ID) VALUES (NEW_ID)
SELECT NEW_ID FROM SRC

I tried with WHEN NEW_ID NOT IN (SELECT...) THEN

But it didn't work and threw an error of unsupported.

Does it create a virtual column in all rows with values of true or false and then add all rows having true as a result?

Answer 1

Using EXISTS:

INSERT ALL
WHEN NOT EXISTS (SELECT 1 FROM DEST WHERE DEST.ID = NEW_ID) THEN
INTO DEST (ID) VALUES (NEW_ID) 
SELECT NEW_ID FROM SRC;

Sample data:

CREATE TABLE DEST(ID INT);
INSERT INTO DEST VALUES(1);

CREATE TABLE SRC(NEW_ID INT);
INSERT INTO SRC VALUES (1),(2);


SELECT * FROM DEST;
-- 1

--EXPLAIN USING TABULAR
INSERT ALL
WHEN NOT EXISTS (SELECT 1 FROM DEST WHERE DEST.ID = NEW_ID) THEN
INTO DEST (ID) VALUES (NEW_ID) 
SELECT NEW_ID FROM SRC;


SELECT * FROM DEST;
-- 1
-- 2