How to use Conditional Insert into Snowflake Tables?

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 all WHEN clauses. If no WHEN clause evaluates to TRUE, then the ELSE clause, if present, executes.
FIRST specifies that each WHEN clause is evaluated in order. The load actions associated with the first WHEN 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 every INTO 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;