COPY INTO versus INSERT INTO on snowflake transformations, with existing tables-CodePudding

So as far as I can tell, it's generally considered more efficient to use COPY INTO versus INSERT INTO in Snowflake. Is this true for existing tables being transformed? And is it even possible for tables already existing in snowflake? For example,

INSERT INTO TEST_TABLE
SELECT *
FROM SOURCE_TABLE_1
UNION ALL
SELECT *
FROM SOURCE_TABLE_2

Doing something like the above query in an insert is very straightforward, but would it technically be more efficient to use a COPY INTO here? And how would the syntax for that work?

COPY INTO TEST_TABLE
FROM (SELECT *
FROM SOURCE_TABLE_1
UNION ALL
SELECT *
FROM SOURCE_TABLE_2)

Doesn't appear to work, is there a way to get it to do so? Thanks,just trying to learn :)

CodePudding user response：

COPY INTO has two flavours:

data ingestion: COPY INTO table
data unloading COPY INTO location

Both uses named internal/external stage or storage location as one side of operation.

Thus COPY INTO is not inteded to perform data movement between tables already present in Snowflake.

COPY INTO TEST_TABLE
FROM (SELECT *
FROM SOURCE_TABLE_1
UNION ALL
SELECT *
FROM SOURCE_TABLE_2)

Assuming that source_table1 and source_table_2 are stages and not pernament table, it will not work either. Snowflake supports subset of operations during data load: Transforming Data During a Load

The COPY command supports:

Column reordering, column omission, and casts using a SELECT statement. There is no requirement for your data files to have the same number and ordering of columns as your target table.

The ENFORCE_LENGTH | TRUNCATECOLUMNS option, which can truncate text strings that exceed the target column length.

CodePudding user response：

There is a fundamental difference between COPY and INSERT.

COPY is used to load data from a staged file to a Snowflake table. This means you are loading the file from either an internal or an external stage into your Snowflake target table: https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html

INSERT is used to load a table in Snowflake with - like in your example - data from another table. But note: The source data is in a Snowflake table already and not a file in one of your stages: https://docs.snowflake.com/en/sql-reference/sql/insert.html

This means: Your first SQL query would work, your second one - in case SOURCE_TABLE_1 and _2 are permanent tables - would fail.