A Boolean property that determines whether INSERT statements are automatically converted to MERGE statements when executed. This property is applicable only when the INSERT operation includes a table’s primary key field. When enabled, Snowflake attempts to upsert records by merging incoming data with existing rows if a primary key conflict occurs, rather than performing a simple insert operation.

Data Type

bool

Default Value

false

Remarks

MergeInsert allows for upsert functionality. This property is primarily intended for use with tools where you have no direct control over the queries being executed. Otherwise, as long as Query Passthrough is 'true', you could execute the MERGE command directly.

When this property is 'false', INSERT statements are executed directly against the server. When it is set to 'true' and the INSERT query contains the primary key field, the Snowflake sends a MERGE query that executes an INSERT if no match is found in Snowflake or an UPDATE if it is found. For example this query:

INSERT INTO "Table" ("ID", "NAME", "AGE") VALUES (1, 'NewName', 10)

MERGE INTO "Table" AS "Target" USING (SELECT 1 AS "ID") AS [Source] ON ("Target"."ID" = "Source"."ID") 
WHEN NOT MATCHED THEN INSERT ("ID", "NAME", "AGE") VALUES (1, 'NewName', 10) 
WHEN MATCHED THEN UPDATE SET "NAME" = 'NewName', "AGE" = 10