SNOW-889678: Support MERGE INTO .. USING (SELECT * FROM ( VALUES .. ) )

[markfickett]

What is the current behavior?

The MergeInto command expects the source to be from an existing table.

What is the desired behavior?

It would be great to have simple syntax for pulling from VALUES.

If there's already a way to do this with a CTE or a temporary table, having an example of that approach in the docs would be very helpful as well.

For example, something like:

from sqlalchemy.sql import Values
from sqlalchemy import column, Integer

source = Values(column('t2key', Integer), column('newval', Integer), name='sq').data([(1, 11), (2, 22), (3, 33)]))
merge = MergeInto(target=t1, source=source, on=t1.c.t1key == source.c.t2key)
merge.when_not_matched_then_insert().values(val=source.c.newval)
num_inserted = connection.execute(merge).scalar()

However, I'm not sure if or how table and column references would work with a Values.

How would this improve snowflake-connector-python?

This would make it easier to use MergeInto when the source data is not already in a table.

References, Other Background

• SO question about VALUES clauses in SqlAlchemy
• SO question about temp tables in SqlAlchemy

[GianlucaGuarroSonos]

Any update on this? Is the intention for me to create a temporary table with the data I have in my python script and then use this merge into function with this temp table as the source?

[sfc-gh-dszmolka]

hi all and thank you for this interest in this new capability - at this moment there's no timeline available for the implementation but will update this thread as soon as any new information becomes available.