INSERT

Description

Inserts new rows into a table. (Redshift SQL Language Reference Insert Statement).

This syntax is partially supported in Snowflake.

Grammar Syntax

INSERT INTO table_name [ ( column [, ...] ) ]
{DEFAULT VALUES |
VALUES ( { expression | DEFAULT } [, ...] )
[, ( { expression | DEFAULT } [, ...] )
[, ...] ] |
query }

Sample Source Patterns

Setup data

Redshift

CREATE TABLE employees (
    id INTEGER IDENTITY(1,1),
    name VARCHAR(100),
    salary INT DEFAULT 20000,
    department VARCHAR(50) DEFAULT 'Marketing'
);

CREATE TABLE new_employees (
    name VARCHAR(100),
    salary INT,
    department VARCHAR(50)
);

INSERT INTO new_employees (name, salary, department)
VALUES
    ('Grace Lee', 32000, 'Operations'),
    ('Hannah Gray', 26000, 'Finance');

Default Values

It inserts a complete row with its default values. If any columns do not have default values, NULL values are inserted in those columns.

This clause cannot specify individual columns; it always inserts a complete row with its default values. Additionally, columns with the NOT NULL constraint cannot be included in the table definition. To replicate this behavior in Snowflake, SnowConvert insert a column with a DEFAULT value in the table. This action inserts a complete row, using the default value for every column.

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE TABLE employees (
    id INTEGER IDENTITY(1,1),
    name VARCHAR(100),
    salary INT DEFAULT 20000,
    department VARCHAR(50) DEFAULT 'Marketing'
);

INSERT INTO employees
DEFAULT VALUES;

SELECT * FROM employees ORDER BY id;

Result

ID	NAME	SALARY	DEPARTMENT
1	NULL	20000	Marketing

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE TABLE employees (
    id INTEGER IDENTITY(1,1) ORDER,
    name VARCHAR(100),
    salary INT DEFAULT 20000,
    department VARCHAR(50) DEFAULT 'Marketing'
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}';

INSERT INTO employees (id)
VALUES (DEFAULT);

SELECT * FROM
    employees
ORDER BY id;

Result

ID	NAME	SALARY	DEPARTMENT
1	NULL	20000	Marketing

Query

Insert one or more rows into the table by using a query. All rows produced by the query will be inserted into the table. The query must return a column list that is compatible with the table’s columns, although the column names do not need to match. This functionality is fully equivalent in Snowflake.

Input Code:

Redshift

IN -> Redshift_02.sql

INSERT INTO employees (name, salary, department)
SELECT name, salary, department FROM new_employees;

Result

ID	NAME	SALARY	DEPARTMENT
1	Grace Lee	32000	Operations
2	Hannah Gray	26000	Finance

Output Code:

Snowflake

OUT -> Redshift_02.sql

INSERT INTO employees (name, salary, department)
SELECT name, salary, department FROM
    new_employees;

Result

ID	NAME	SALARY	DEPARTMENT
1	Grace Lee	32000	Operations
2	Hannah Gray	26000	Finance

Known Issues

• Certain expressions cannot be used in the VALUES clause in Snowflake. For example, in Redshift, the JSON_PARSE function can be used within the VALUES clause to insert a JSON value into a SUPER data type. In Snowflake, however, the PARSE_JSON function cannot be used in the VALUES clause to insert a JSON value into a VARIANT data type. Instead, a query can be used in place of the VALUES clause. For more details, please refer to the Snowflake documentation. You can also check the following article for further information.

Related EWIs

There are no known issues.