FireDAC Components for Snowflake

Build 21.0.7958

MergeInsert

A boolean indicating whether INSERT statements should be converted to MERGE statements automatically. Only used when the INSERT contains a table's primary key field.

Syntax


property MergeInsert: Boolean;

Data Type

Boolean

Default Value

false

Remarks

A boolean indicating whether INSERT statements should be converted to MERGE statements automatically to allow 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 will send a MERGE query that will execute an INSERT if no match is found in Snowflake or an UPDATE if it is. For example this query:

INSERT INTO "Table" ("ID", "NAME", "AGE") VALUES (1, 'NewName', 10)
Will be sent to Snowflake as the following MERGE request:
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

Copyright (c) 2021 CData Software, Inc. - All rights reserved.
Build 21.0.7958