INSERT (multi-table)¶
Updates multiple tables by inserting one or more rows with column values (from a query) into the tables. Supports both unconditional and conditional inserts.
- See also:
Syntax¶
-- Unconditional multi-table insert
INSERT [ OVERWRITE ] ALL
intoClause [ ... ]
<subquery>
-- Conditional multi-table insert
INSERT [ OVERWRITE ] { FIRST | ALL }
{ WHEN <condition> THEN intoClause [ ... ] }
[ ... ]
[ ELSE intoClause ]
<subquery>
Where:
intoClause ::= INTO <target_table> [ ( <target_col_name> [ , ... ] ) ] [ VALUES ( { <source_col_name> | DEFAULT | NULL } [ , ... ] ) ]
Required parameters¶
ALL
Unconditional multi-table insert only
Specifies that each row executes every
INTO
clause in the INSERT statement.Note
If the
FIRST
keyword is specified in an unconditional multi-table insert (or theALL
keyword is not specified), Snowflake returns a syntax error.FIRST
orALL
Conditional multi-table insert only
FIRST
Specifies that each row executes only the first
WHEN
clause for which the condition evaluates to TRUE. If noWHEN
clause evaluates to TRUE, then theELSE
clause, if present, executes.ALL
Specifies that each row executes all
WHEN
clauses. If noWHEN
clause evaluates to TRUE, then theELSE
clause, if present, executes.
Note
A conditional multi-table insert must contain at least one
WHEN
clause.Each
WHEN
clause can contain multipleINTO
clauses and theINTO
clauses can insert into the same target table.To always execute a
WHEN
clause, use:WHEN 1=1 THEN ...
condition
Conditional multi-table insert only
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.target_table
Specifies a target table into which to insert rows. The same table may be referenced more than once (in separate
WHEN
clauses).Multiple tables can be targeted by including a
INTO
clause for each table.subquery
Specifies the SELECT list that determines the source of the values to be inserted into the target tables.
Optional parameters¶
OVERWRITE
Specifies to truncate the target tables before inserting into the tables, while retaining access control privileges on the tables.
INSERT statements with
OVERWRITE
can be processed within the scope of the current transaction, which avoids DDL statements that commit a transaction, such as:DROP TABLE t; CREATE TABLE t AS SELECT * FROM ... ;
Default: No value (the target tables are not truncated before performing the inserts)
( target_col_name [ , ... ] )
Specifies one or more columns in the target table into which the values from the corresponding column in the source is inserted. The number of target columns specified must match the number of values specified in the source.
Default: No value (all the columns in the target table are updated)
VALUES ( source_col_name | DEFAULT | NULL [ , ... ] )
Specifies one or more values to insert into the corresponding columns in the target table. The values can be:
source_col_name
: Specifies the column in the source that contains the value to be inserted into the corresponding column in the target table.DEFAULT
: Inserts the default value for the corresponding column in the target table.NULL
: Inserts aNULL
value.
Each value in the clause must be separated by a comma. Also, the number of values specified must match the number of columns specified for the target table.
Default: No value (values from all the columns in the source are inserted into the corresponding columns in the target table)
Usage notes¶
In an
INTO
clause, theVALUES
clause is optional. If it is omitted, the values from the SELECT list are inserted into the target table in their natural order.Expressions in
WHEN
clauses (for conditional multi-table inserts) andVALUES
clauses can only reference the subquery via an alias. The alias must be one of the following:Explicit alias specified for a SELECT expression.
Default alias for an expression.
Positional alias ($1, $2, etc.).
In addition, columns and expressions of the subquery that are not in the outermost SELECT list can not be referenced in
WHEN
andVALUES
clauses. For details, see Examples (in this topic).In each row produced by the
subquery
, the value insource_col_name
must be compatible with the data type of the correspondingtarget_col_name
. This rule applies even to rows that would be filtered out by thecondition
in theWHEN
clause. The order of operations does not guarantee that the filter in theWHEN
clause is applied before the value insource_col_name
is evaluated for data type compatibility.
Examples¶
Unconditional multi-table inserts¶
Insert each row in the src
table twice into tables t1
and t2
. In this example, the inserted rows are not
identical; each of the inserted rows has different values/orders because we use the VALUES clause to vary the data:
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 src; -- If t1 and t2 need to be truncated before inserting, OVERWRITE must be specified 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 src;
Conditional multi-table inserts¶
The next two examples show how to create conditional multi-table inserts by
using WHEN
clauses and an ELSE
clause to decide which table(s), if any, each row is inserted into.
These examples also show the difference between using INSERT ALL
and
INSERT FIRST
.
Execute all WHEN
clauses with an ELSE
clause:
Rows where
n1 > 100
also satisfy the conditionn1 > 10
and are therefore inserted int1
twice when theALL
keyword is used.Rows where
n1 <= 10
satisfy theELSE
case and are inserted int2
.INSERT ALL WHEN n1 > 100 THEN INTO t1 WHEN n1 > 10 THEN INTO t1 INTO t2 ELSE INTO t2 SELECT n1 from src;
If the table src contains 3 rows, in which n1 has the values 1, 11, and 101, then after the INSERT statement the tables t1 and t2 will hold the values shown below:
t1:
101 |
101 > 100, so the first |
101 |
101 > 10, so the second |
11 |
11 > 10, so the second |
The row with n1 = 1
is not inserted into t1 because it does not satisfy
any WHEN
clause that inserts into t1, and because the ELSE
clause does not insert into t1.
t2:
101 |
101 > 10, so the second |
11 |
11 > 10, so the second |
1 |
the row didn’t satisfy any of the |
The next example is similar to the previous example, except with a FIRST
clause.
INSERT FIRST WHEN n1 > 100 THEN INTO t1 WHEN n1 > 10 THEN INTO t1 INTO t2 ELSE INTO t2 SELECT n1 from src;
If the table src contains 3 rows, in which n1 has the values 1, 11, and 101, then after the INSERT statement the tables t1 and t2 will hold the values shown below:
t1:
101 |
101 > 100, so the first |
11 |
11 > 10, so the second |
The row with n1 = 1
is not inserted into t1 because it does not satisfy any WHEN
clause that inserts into t1, and because
the ELSE
clause does not insert into t1.
Unlike in the previous example, which used ALL
, the row with n1 = 101
is inserted into t1 only once because the first
WHEN
clause evaluates to TRUE so the second WHEN
clause is ignored.
t2:
11 |
11 > 10, so the second |
1 |
the row didn’t satisfy any of the |
The row n1 = 101
is not inserted into t2 because 101 is greater than 100, so it matches the first WHEN
clause, but the
first WHEN
clause doesn’t insert into t2, and the statement doesn’t check any of the other WHEN
clauses or use the
ELSE
clause because the row already qualified for the first WHEN
clause.
Multi-table inserts with aliases and references¶
Insert values using a positional alias ($1
), an explicit alias (an_alias
), and a default alias ("10 + 20"
);
this example inserts a single row with values (1, 50, 30)
into table t1
:
INSERT ALL INTO t1 VALUES ($1, an_alias, "10 + 20") SELECT 1, 50 AS an_alias, 10 + 20;
Illustrate inserting values from columns that must be selected to be referenced (b
and c
in table src
):
-- Returns error INSERT ALL WHEN c > 10 THEN INTO t1 (col1, col2) VALUES (a, b) SELECT a FROM src; -- Completes successfully INSERT ALL WHEN c > 10 THEN INTO t1 (col1, col2) VALUES (a, b) SELECT a, b, c FROM src;
Illustrate inserting values from a column that cannot be referenced (src1.key
); instead, it must be selected and aliased:
-- Returns error INSERT ALL INTO t1 VALUES (src1.key, a) SELECT src1.a AS a FROM src1, src2 WHERE src1.key = src2.key; -- Completes successfully INSERT ALL INTO t1 VALUES (key, a) SELECT src1.key AS key, src1.a AS a FROM src1, src2 WHERE src1.key = src2.key;