snowflake.snowpark.WhenNotMatchedClause.insert¶

WhenNotMatchedClause.insert(assignments: Union[Iterable[Union[Column, None, bool, int, float, str, bytearray, Decimal, date, datetime, time, bytes, list, tuple, dict]], Dict[str, Union[Column, None, bool, int, float, str, bytearray, Decimal, date, datetime, time, bytes, list, tuple, dict]]]) → WhenNotMatchedClause[source]¶

Defines an insert action for the not-matched clause and returns an updated WhenNotMatchedClause with the new insert action added.

Parameters:: assignments – A list of values or a dict that associates the names of columns with the values that should be inserted. The value of assignments can either be a literal value or a Column object.

Examples:

>>> # Adds a not-matched clause where a row in source is not matched
>>> # if its key does not equal the key of any row in target.
>>> # For all such rows, insert a row into target whose ley and value
>>> # are assigned to the key and value of the not matched row.
>>> from snowflake.snowpark.functions import when_not_matched
>>> from snowflake.snowpark.types import IntegerType, StringType, StructField, StructType
>>> schema = StructType([StructField("key", IntegerType()), StructField("value", StringType())])
>>> target_df = session.create_dataframe([(10, "old"), (10, "too_old"), (11, "old")], schema=schema)
>>> target_df.write.save_as_table("my_table", mode="overwrite", table_type="temporary")
>>> target = session.table("my_table")
>>> source = session.create_dataframe([(12, "new")], schema=schema)
>>> target.merge(source, target["key"] == source["key"], [when_not_matched().insert([source["key"], source["value"]])])
MergeResult(rows_inserted=1, rows_updated=0, rows_deleted=0)
>>> target.sort("key", "value").collect() # the rows are inserted
[Row(KEY=10, VALUE='old'), Row(KEY=10, VALUE='too_old'), Row(KEY=11, VALUE='old'), Row(KEY=12, VALUE='new')]

>>> # For all such rows, insert a row into target whose key is
>>> # assigned to the key of the not matched row.
>>> target_df.write.save_as_table("my_table", mode="overwrite", table_type="temporary")
>>> target.merge(source, target["key"] == source["key"], [when_not_matched().insert({"key": source["key"]})])
MergeResult(rows_inserted=1, rows_updated=0, rows_deleted=0)
>>> target.sort("key", "value").collect() # the rows are inserted
[Row(KEY=10, VALUE='old'), Row(KEY=10, VALUE='too_old'), Row(KEY=11, VALUE='old'), Row(KEY=12, VALUE=None)]

Note

An exception will be raised if this method is called more than once on the same WhenNotMatchedClause object.