INSERT INTO in Snowflake: A Complete Beginner’s Guide

When working with Snowflake, one of the fundamental operations you'll perform is inserting data into tables. The INSERT INTO statement allows you to add new records into a table, whether manually entering values, inserting multiple rows, or pulling data from other tables.

In this guide, I will walk you through the syntax, various use cases, best practices, and common troubleshooting steps for using INSERT INTO effectively in Snowflake.

What is the INSERT INTO Command in Snowflake?

Before discussing the syntax, we must first understand the purpose of INSERT INTO in Snowflake and its different use cases. 

Adding data to a database is a critical part of managing a database. The INSERT INTO statement in Snowflake is used to add new rows of data into a table. It can insert single rows, multiple rows, or bulk data from another table. You can either insert data by manually typing each value for a particular row, or set of rows, or use a SQL statement to bulk load data from other data sources.

> While INSERT INTO is a fundamental command, ensuring you understand Snowflake and its architecture is a great starting point. Check out this tutorial on Snowflake to brush up on fundamentals!

Use cases for INSERT INTO

Here are some of the common use cases for the INSERT INTO clause. 

• Adding new records manually to a table.
• Bulk inserting multiple rows in a single query.
• Transferring data from one table to another.
• Appending new data to an existing dataset.
• Populating a table from the results of a SELECT query.

> Learning this clause is great for Snowflake interviews as well!

Syntax of INSERT INTO in Snowflake

Now we get into the actual syntax. Let's first discuss the most straightforward usage of INSERT INTO: simply adding data to a row by manually inputting the values.

The basic syntax for INSERT INTO is as follows:

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

You write out the clause INSERT INTO followed by the table_name, which is where we are inserting the data. To be specific about the data order, we can specify the names of the columns that follow in parentheses as (column1, column2, column3). The final portion is writing out the VALUES (value1, value2, value3), which will be inserted into the respective columns.

Inserting data without specifying column names

If you omit column names, Snowflake assumes values are provided in the same order as the table’s schema. Meaning, the first value will be assumed to be for the first column in the table, and so on.

INSERT INTO table_name
VALUES (value1, value2, value3);

With this approach, you must make sure the values are in the same order as the columns, or you may run into some issues with data types or data consistency.

Advanced Usage of INSERT INTO in Snowflake

Some of the more advanced usages of INSERT INTO include inserting a single row from a different table, inserting multiple rows, or inserting data with conditions. This is a useful way of adding data to a table or archiving data in a separate table.

Using INSERT INTO with multiple rows

Instead of inserting one row at a time, you can insert multiple rows in a single statement:

INSERT INTO employees (id, name, department)
VALUES
    (1, 'Alice', 'HR'),
    (2, 'Bob', 'Finance'),
    (3, 'Charlie', 'IT');

This approach reduces query execution overhead and improves performance. Note that each row is completely enclosed by a set of parentheses and followed by a comma. Not doing this can lead to errors. 

Inserting data from another table

To copy data from one table to another, use INSERT INTO with a SELECT statement:

INSERT INTO target_table (column1, column2)

SELECT
column1, 
column2 
FROM source_table ;

This is useful for archiving data, transforming data before insertion, or appending filtered records. If the source data is in the same column order as the target, then you can omit naming the columns next to the target_table.

Conditional inserts

You can insert data based on a condition in your SELECT statement, so you are not copying an entire table.

INSERT INTO high_salary_employees (id, name, salary)

SELECT 
id, 
name, 
salary
FROM employees 
WHERE salary > 100000;

This ensures that only employees meeting the condition are inserted into the new table. Doing so helps maintain data integrity and reduces storage and computational overhead.

Best Practices for Using INSERT INTO in Snowflake

Here are some best practices when using INSERT INTO in Snowflake. This includes using more efficient statements like MERGE and validating data before insertion.

Using MERGE instead of INSERT INTO for upserts

If you need to insert new records but update existing ones, MERGE is a better option because it can match existing data. 

It looks for where current rows meet a certain criteria (for instance, an existing employee ID) and can update specific values in the target table using a source table. When there is no match, then it will insert the new data. This is a concept known as “upsert” (update and insert). 

See below for an example of how to use MERGE:

MERGE INTO target_table AS t
USING source_table AS s
ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET t.salary = s.salary
WHEN NOT MATCHED THEN
    INSERT (id, name, salary) VALUES (s.id, s.name, s.salary);

> The MERGE statement is quickly becoming standard procedure and is a key part of Snowflake certifications.

Batch inserts and performance

To improve performance when inserting large datasets, try to focus on performing batch inserts. This reduces overhead by not having to spin up new connections with each load and improves overall performance. Here are some tips for batch inserting:

• Use bulk inserts instead of inserting row by row.
• Use COPY INTO when loading large data from external sources. This is useful when connecting to something like S3.
• Avoid inserting data within loops in scripts; batch processing is more efficient. So finish the loop, store the data as a single data source, and then load that entire data source at once.

Data validation before inserting

Before inserting data, validate it. It can cause many issues, or outright failure, if your data is not validated. Think about how the data might be used downstream and what your inserted data should look like. Here are some key things to look out for:

• Data types match the column definitions.
• Required fields are not null.
• No duplicate primary keys or unique constraint violations.

Error Handling with INSERT INTO in Snowflake

As stated before, there can be the occasional error when inserting data. Here is some advice on how to deal with common errors. 

Dealing with duplicate keys

Snowflake itself does not necessarily enforce unique constraints, meaning it is possible to insert duplicate data. 

If you are concerned about duplicate keys, it may be better to consider handling it with MERGE or ON CONFLICT (if supported). Ultimately, you can also add a condition to your insert using WHERE to exclude anything existing in the target table.

INSERT INTO employees (id, name)
SELECT id, name FROM new_employees
WHERE id NOT IN (SELECT id FROM employees);

Handling NULL values

Ensure that nullable columns allow NULL values or provide default values. Snowflake does enforce any NOT NULL column constraints, so if you have NULL data in a column with that constraint, your insert will fail. The most important thing here is understanding your data, where NULLs are allowed to exist, and where they are not.

Troubleshooting Common Issues with INSERT INTO

Some other issues can boil down to data types or permissions issues. We’ll briefly discuss how to handle these issues.

Data type mismatches

Errors occur when inserting a value of the wrong data type. You can sometimes deal with this by using type-casting your data, but that can lead to NULLs. To make sure your inserted data is the same as the target, you can try looking at the information_schema (where you have permissions) and assess your schema that way:

SELECT column_name, 
data_type
 FROM information_schema.columns 
WHERE table_name = 'employees';

Permissions issues

If you receive a permission error, check user roles and privileges:

SHOW GRANTS TO USER current_user;

If you do not have the permission to use INSERT, check with your manager, your IT department, or whoever is the owner of the dataset. If you are able to grant yourself permissions, then use the following code:

GRANT INSERT ON TABLE employees TO user_name;

Conclusion

The INSERT INTO command in Snowflake is a powerful tool for efficiently adding data to tables. Whether inserting single rows, bulk data, or copying from another table, understanding its syntax and best practices ensures data integrity and performance. You can maintain a robust and efficient Snowflake database by handling errors, validating data, and optimizing inserts. 

To learn more about Snowflake, I recommend checking the following courses:

• Introduction to Snowflake
• Introduction to Data Modeling in Snowflake