Updates multiple tables by inserting one or more rows with column values (from a query) into the tables. Supports both unconditional and conditional inserts.
-- Unconditional multi-table insert
INSERT [ OVERWRITE ] ALL
intoClause [ ... ]
<subquery>
-- Conditional multi-table insert
INSERT [ OVERWRITE ] { FIRST | ALL }
{ WHEN <condition> THEN intoClause [ ... ] }
[ ... ]
[ ELSE intoClause ]
<subquery>
Copy
Where:
Required parameters¶intoClause ::= INTO <target_table> [ ( <target_col_name> [ , ... ] ) ] [ VALUES ( { <source_col_name> | DEFAULT | NULL } [ , ... ] ) ]Copy
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 the ALL keyword is not specified), Snowflake returns a syntax error.
FIRST or ALL
Conditional multi-table insert only
FIRST
Specifies that each row executes only the first WHEN clause for which the condition evaluates to TRUE. If no WHEN clause evaluates to TRUE, then the ELSE clause, if present, executes.
ALL
Specifies that each row executes all WHEN clauses. If no WHEN clause evaluates to TRUE, then the ELSE clause, if present, executes.
Note
A conditional multi-table insert must contain at least one WHEN clause.
Each WHEN clause can contain multiple INTO clauses and the INTO 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.
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 ... ;Copy
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 a NULL 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)
In an INTO clause, the VALUES 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) and VALUES 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 and VALUES clauses. For details, see Examples (in this topic).
In each row produced by the subquery, the value in source_col_name must be compatible with the data type of the corresponding target_col_name. This rule applies even to rows that would be filtered out by the condition in the WHEN clause. The order of operations does not guarantee that the filter in the WHEN clause is applied before the value in source_col_name is evaluated for data type compatibility.
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:
Conditional multi-table inserts¶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;Copy
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 condition n1 > 10 and are therefore inserted in t1 twice when the ALL keyword is used.
Rows where n1 <= 10 satisfy the ELSE case and are inserted in t2.
INSERT ALL
WHEN n1 > 100 THEN
INTO t1
WHEN n1 > 10 THEN
INTO t1
INTO t2
ELSE
INTO t2
SELECT n1 from src;
Copy
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 WHEN clause inserts into t1
101
101 > 10, so the second WHEN clause also inserts into t1
11
11 > 10, so the second WHEN clause inserts into t1
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 WHEN clause inserts into t2. (The row also qualifies for the clause WHEN n1 > 100; however, that clause does not insert into t2.)
11
11 > 10, so the second WHEN clause inserts into t2
1
the row didn’t satisfy any of the WHEN clauses, so it’s inserted into t2 by the ELSE clause
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;Copy
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 WHEN clause inserts into t1
11
11 > 10, so the second WHEN clause inserts into t1
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 WHEN clause inserts into t2
1
the row didn’t satisfy any of the WHEN clauses, so it’s inserted into t2 by the ELSE clause
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.
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;Copy
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;Copy
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;Copy
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4