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?

user_3922727 0
Viewed 151

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?

data-ingestion snowflake-cloud-data-platform bulkinsert insert sql
1 Answers

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
user_50708790
Related