INSERT and Multi-Table Inserts in Snowflake

1. INSERT Command in Snowflake

The INSERT command in Snowflake is used to add new rows of data to a database table. Unlike traditional databases, Snowflake offers advanced functionalities for inserting single or multiple records into table data.

In this article, let us explore the syntax, usage, and practical examples of the INSERT command in Snowflake.

Syntax:

INSERT INTO <table_name>(col1, col2,…)
       VALUES(value1, value2,…)

A table named “EMPLOYEES” is created for demonstration purposes using the following DDL statement. This table will be referenced in the examples discussed in the article.

CREATE OR REPLACE TABLE EMPLOYEES(
    ID NUMBER,
    NAME VARCHAR(50),
    SALARY NUMBER
);

The following SQL statement inserts a single record into the EMPLOYEES table.

--INSERTING A RECORD INTO A TABLE
-------------------------------------------
INSERT INTO EMPLOYEES(ID, NAME, SALARY)
    VALUES(100,'TONY',6000);

The following image shows the contents of the EMPLOYEES table after loading data using the INSERT statement.

2. Inserting Multiple Records using a Single INSERT Statement

Instead of creating a separate INSERT statement for each record that needs to be added to a table, Snowflake supports inserting multiple records into a table with a single INSERT statement by specifying additional sets of values separated by commas in the VALUES clause.

The following SQL statement inserts multiple records into the EMPLOYEES table using a single INSERT statement.

--INSERTING MULTIPLE RECORDS INTO A TABLE WITH A SINGLE INSERT STATEMENT
-------------------------------------------------------------------------
INSERT INTO EMPLOYEES(ID, NAME, SALARY)
  VALUES
    (101,'STEVE',4000),
    (102,'BRUCE',5000),
    (103,'SCOTT',8000),
    (104,'PETER',3000),
    (105,'WANDA',7000)
;

The following image shows the contents of the EMPLOYEES table after loading multiple records using the INSERT statement.

3. Inserting Multiple Records using SELECT Statement

Multiple records can be inserted into a table using the “INSERT” command in conjunction with a “SELECT” statement. This helps in copying data from one table to another existing table.

The following SQL statements create a new table named “EMP” and copies data from the “EMPLOYEES” table using the INSERT INTO SELECT statement.

--INSERT RECORDS INTO A TABLE USING SELECT STATEMENT
------------------------------------------------------
CREATE OR REPLACE TABLE EMP(
    ID NUMBER,
    NAME VARCHAR(50),
    SALARY NUMBER
);

INSERT INTO EMP(ID, NAME, SALARY)
    SELECT ID, NAME, SALARY FROM EMPLOYEES;

The following image shows the contents of the EMP table after loading data using the INSERT INTO SELECT statement.

4. Truncate Records before Inserting data with the INSERT OVERWRITE Statement

INSERT statements with the “OVERWRITE” command help in truncating the data in the table before inserting values into it.

The following SQL statement truncates data in the EMP table before inserting data into it.

--TRUNCATE TABLE BEFORE INSERTING DATA
-----------------------------------------------
INSERT OVERWRITE INTO EMP(ID, NAME, SALARY)
    SELECT * FROM EMPLOYEES;

This helps in avoiding the execution of additional DDL statements such as below before inserting data into the table:

--TRUNCATE TABLE AND INSERT DATA
-----------------------------------------------
TRUNCATE TABLE EMP;
INSERT INTO EMP
    SELECT * FROM EMPLOYEES;
    
--RECREATE TABLE WITH NEW DATA
-----------------------------------------------
CREATE OR REPLACE TABLE EMP AS
       SELECT * FROM EMPLOYEES;

5. Inserting Records Into Multiple Tables with INSERT ALL Statement

Snowflake supports inserting multiple records into multiple tables at once using the “INSERT ALL” statement. The insert statements can be either unconditional or conditional.

For the demonstration of Multi-table inserts, the following tables are created.

CREATE OR REPLACE TABLE EMP_1(
    EMP_ID NUMBER,
    EMP_NAME VARCHAR(50),
    EMP_SALARY NUMBER
);

CREATE OR REPLACE TABLE EMP_2(
    EMP_ID NUMBER,
    EMP_NAME VARCHAR(50),
    EMP_SALARY NUMBER
);

CREATE OR REPLACE TABLE EMP_3(
    EMP_ID NUMBER,
    EMP_NAME VARCHAR(50),
    EMP_SALARY NUMBER
);

CREATE OR REPLACE TABLE EMP_4(
    EMP_ID NUMBER,
    EMP_NAME VARCHAR(50),
    EMP_SALARY NUMBER
);

5.1. Unconditional Multi-Table Inserts

The following SQL statement inserts data into multiple tables at once using the INSERT ALL command.

--UNCONDITIONAL MULTI-TABLE INSERT
-----------------------------------------------
INSERT ALL
    INTO EMP_1
    INTO EMP_2(EMP_ID, EMP_NAME, EMP_SALARY) VALUES(ID, LOWER(NAME), SALARY)
    INTO EMP_3(EMP_ID, EMP_NAME, EMP_SALARY)
    INTO EMP_4 VALUES(ID, NAME, SALARY*1.1)
SELECT ID, NAME, SALARY FROM EMPLOYEES;

The following image shows the output of the Unconditional Multi-table Insert Statement.

The data is inserted into EMP1, EMP2, EMP3 and EMP4 simultaneously from the EMPLOYEES table using the INSERT ALL statement.

• The VALUES clause is optional in the INTO clause. If it is omitted (as with EMP1 & EMP3), values from the SELECT query are inserted into the target table in their natural order.
• Use the VALUES clause when the order of columns is not in sync or to transform the data while loading it using the specified SELECT statement.

The following images show the contents of EMP_2 and EMP_4 tables after loading data using Unconditional Multi-table Insert Statement.

5.2. Conditional Multi-Table Inserts

Conditional Multi-table inserts can be performed using “WHEN” and “ELSE” clauses in the INSERT ALL statement.

The following statement inserts data into multiple tables based on conditions defined in the INSERT ALL statement.

--CONDITIONAL MULTI-TABLE INSERT
-----------------------------------------------
INSERT ALL
    WHEN SALARY <= 5000 THEN
        INTO EMP_1
    WHEN SALARY BETWEEN 5000 AND 7000 THEN
        INTO EMP_2
    ELSE
        INTO EMP_3
        INTO EMP_4
SELECT ID, NAME, SALARY FROM EMPLOYEES;

The following image shows the output of the Conditional Multi-table Insert Statement using the INSERT ALL statement.

In the above query:

• The data of employees with a salary <= 5000 are inserted into the EMP_1 table.
• The data of employees with salary BETWEEN 5000 AND 7000  are inserted into the EMP_2 table
• The records that do not match any of the conditions defined in the WHEN clause are loaded into EMP_3 and EMP_4 tables.

In the above conditional Multi-table insert using INSERT ALL, we can observe that the record with SALARY=5000 is satisfied by conditions specified in multiple WHEN clauses. The record is inserted into both EMP_1 and EMP_2 tables.

The following images show the contents of EMP_1, EMP_2, EMP_3 and EMP_4 tables after loading data using the INSERT ALL statement.

Note that a conditional multi-table insert must contain at least one WHEN clause. The WHEN/ELSE clauses can contain multiple INTO clauses.

5.3. Conditional Multi-Table Inserts using INSERT FIRST

The “INSERT FIRST” statements ignore a row in the WHEN clause which is already satisfied by a condition in a previous WHEN clause.

--Multi-Table Inserts using INSERT FIRST
-------------------------------------------
INSERT FIRST
    WHEN SALARY <= 5000 THEN
        INTO EMP_1
    WHEN SALARY BETWEEN 5000 AND 7000 THEN
        INTO EMP_2
    ELSE
        INTO EMP_3
        INTO EMP_4
SELECT ID, NAME, SALARY FROM EMPLOYEES;

The following image shows the output of the Conditional Multi-table Insert Statement using the INSERT FIRST statement.

In the above example:

• The record with SALARY = 5000 is inserted into the EMP_1 table only.
• Since the first WHEN clause evaluates to TRUE, the second WHEN clause is ignored and the record with SALARY = 5000 is not inserted into the EMP_2 table.

The following images show the contents of EMP_1 and EMP_2 tables after loading data using the INSERT FIRST statement.

5.4. Multi-Table Inserts with OVERWRITE command

Similar to INSERT statements, the “OVERWRITE” command can be used in conjunction with INSERT ALL or INSERT FIRST in multi-table insert statements.

INSERT OVERWRITE ALL
      ……
INSERT OVERWRITE FIRST
      ……

Subscribe to our Newsletter !!

Related Articles: