Snowflake cloud data warehouse supports many useful features that are not yet available in many other similar databases. One of such features is multi-table INSERT. You can insert one or more rows from query into one or more table. Syntax support both conditional and unconditional inserts. This feature is useful when you are inserting data using some conditions such as insert only positive rows to the particular table. In this article, we will check how to use conditional and unconditional insert into Snowflake tables, its syntax and examples.
Conditional and Unconditional Insert into Snowflake Tables
Snowflake multi-table insert supports two main options.
- Conditional Insert into Snowflake Tables
- Unconditional Insert into Snowflake Tables
Now, let us check these two options brifely.
Conditional Insert into Snowflake Tables
In the conditional multi-table insert, condition specifies the condition that must evaluate to TRUE in order for the values specified in the INTO
clause to be inserted. The condition can be a SELECT list.
The conditional multi-table insert include WHEN
clauses and an ELSE
clause to decide which table(s), if any, each row is inserted into. Note that, this conditional multi-table insert is different from a MERGE statement which also support conditional insert into a single table.
Conditional Multi-table Insert Syntax
Following piece of code is the conditional multi-table insert syntax.
INSERT [ OVERWRITE ] { FIRST | ALL }
{ WHEN <condition> THEN intoClause [ ... ] }
[ ... ]
[ ELSE intoClause ]
<subquery>
Where,
ALL
specifies that each row evaluates allWHEN
clauses. If noWHEN
clause evaluates to TRUE, then theELSE
clause, if present, executes.FIRST
specifies that each WHEN clause is evaluated in order. The load actions associated with the firstWHEN
clause to return true are executed and the remaining WHEN clauses are ignored.OVERWRITE
specifies to truncate the target tables before inserting into the tables. It is an optional parameter.
Conditional Multi-table Insert with ALL Option
Following example demonstrates the use of conditional multi-table insert with ALL
option.
insert all
when n1 > 105 then
into t1
when n1 > 15 then
into t1
into t2
else
into t2
select n1 from some_table;
Conditional Multi-table Insert with FIRST Option
Following example demonstrates the use of conditional multi-table insert with FIRST
option.
insert first when n1 > 100 then into t1 when n1 > 10 then into t1 into t2 else into t2 select n1 from some_table;
Conditional Multi-table Insert with VALUES Clause
Following example uses VALUES
clause to insert into the Snowflake table.
insert all
when c > 10 then
into t1 (col1, col2) values (a, b)
select a, b, c from some_table;
Conditional Multi-table Insert with OVERWRITE Option
Finally, following examples uses OVERWRITE
to truncate and insert rows.
insert overwrite all
when c > 10 then
into t1 (col1, col2) values (a, b)
select a, b, c from some_table;
Unconditional Insert into Snowflake Tables
The unconditional multi-table insert simply takes data from the defined source such as subquery and loads this into the specified target tables.
Unconditional Multi-table Insert Syntax
Following piece of code is the unconditional multi-table insert syntax.
INSERT [ OVERWRITE ] ALL
intoClause [ ... ]
<subquery>
Where,
ALL
specifies that each row executes everyINTO
clause in the INSERT statement.OVERWRITE
specifies to truncate the target tables before inserting into the tables. It is an optional parameter.
Snowflake Unconditional Multi-table Insert with ALL Option
Following example uses ALL
clause to insert into the Snowflake table. The example also uses VALUES clause and named column insert.
insert all
into t1
into t1 (c1, c2, c3) values (n2, n1, default)
into t2 (c1, c2, c3)
into t2 values (n3, n2, n1)
select n1, n2, n3 from some_table;
Snowflake Unconditional Multi-table Insert with OVERWRITE Option
Following example uses OVERWRITE
clause to insert into the Snowflake table.
insert overwrite all
into t1
into t1 (c1, c2, c3) values (n2, n1, default)
into t2 (c1, c2, c3)
into t2 values (n3, n2, n1)
select n1, n2, n3 from some_table;
Related Articles,
- Snowflake Update Join Syntax – Update using other Table
- Snowflake Merge Statement Syntax, Usage and Examples
- How to Create Synonym in Snowflake?
Hope this helps 🙂