Snowflake Insert Command: A Comprehensive Guide

Are you using Snowflake as your warehousing solution? Are you looking for ways to modify and grow your tables? If yes? then you’ve landed at the right page! This article teaches you how to efficiently use the Snowflake Insert command. It provides you with a step-by-step guide to help you use the Insert command and insert multiple data records to your existing and newly created Snowflake tables.

Introduction to Snowflake

Snowflake is a fully managed cloud-hosted data warehouse available as Software-as-a-Service (SaaS). Snowflake provides an agile and easy to use data warehouse service and possesses many similarities to a traditional data warehouses with additional capabilities. Snowflake uses a new SQL database engine and AWS Cloud services for storage (S3) and computation (EMR). It performances exceptionally well even with enormous volumes of data.

Key features of Snowflake:

• Low Maintenance: All the maintenance, management, and performance tuning are handled by Snowflake, thereby providing a fully managed service.
• Managed Cloud: Snowflake runs entirely on public cloud instances, and uses virtual compute instances and storage services.
• Highly Secure: Snowflake provides top-notch security for data at rest and on-flight to ensure user information is always safe.
• No Hardware: There is no requirement to set up, configure, install, or manage the hardware.
• Speed: Snowflake uses a concept of MPP (Massive Parallel Processing) to process queries on a virtual warehouse. Each warehouse has its cluster with exceptional scaling capability.
• Time Travel: Snowflake has an extraordinary Time-Travel concept that allows you to access the historical data (the data that has been changed or deleted) at any point within the defined period.
• Separate Storage and Compute Layer: Snowflake uses different storage and computes layers that can scale up or down without affecting each other.

For further information on Snowflake, you can check the official site here.

Prerequisites

• Working knowledge of Snowflake.
• A general idea of the command-line.
• Snowflake account and database.

Using the Snowflake Insert command

The Snowflake Insert command is an excellent way to add data into your tables stored in Snowflake. This command is not only used to insert data into an existing table but also to insert data into a newly created table. The data can be a set of manually typed data records or can even be copied from a particular source.

In addition to the Insert command, Snowflake offers various other features/commands to help simplify your data warehousing experience. Some of these are comprehensive shortcuts, scripting features and high-security shortcuts. For further information on SnowSQL, you can check the official documentation here.

You can use the Snowflake Insert command using the following syntax:

INSERT [ OVERWRITE ] INTO <target_table> [ ( <target_col_name> [ , ... ] ) ]
                                         { { VALUES ( { <value> | DEFAULT | NULL } [ , ... ] ) [ , ( ... ) ] } | <query> }

Fill the following parameters carefully to use the Insert command:

• <target_table>: Enter the name of the table you want to use to insert records.
• <query> or VALUES: This field specifies the data that you want to insert into the table.

You can perform various operations using the Snowflake Insert command:

• Inserting a single record using Snowflake Insert command
• Inserting multiple records using Snowflake Insert command
• Inserting nested data using Snowflake Insert command

Inserting a single record using Snowflake Insert command

You can use the Insert command to add a single row of data as follows:

insert into mytable (col1, col3)
  select to_date('2013-05-08T23:39:20.123'), to_timestamp('2013-05-08T23:39:20.123');

This command inserts data into column1 and column3 of the table “mytable” stored in your Snowflake data warehouse. The column2 remains unaffected as it is not included in the query being used to insert data.

You can now use a select statement to fetch data from the table and see how the table has been modified. You can use the following command:

select * from mytable;

The Select command will retrieve the data stored in the table and generate the following output:

Inserting multiple records using Snowflake Insert command

You can use the Insert command to add multiple records of data to your desired table in Snowflake. First, use the select statement to retrieve the existing data from your table using the following command:

select * from employee;

The Select command will retrieve the data stored in the table and generate the following output:

The employee table currently stores two records. You can now use the Snowflake Insert command and add multiple data records to your table. Use the following command to insert two new employee records:

insert into employee
  values
  ('Lysandra','Reeves','1-212-759-3751','New York',10018),
  ('Michael','Arnett','1-650-230-8467','San Francisco',94116);

Use the Select statement to retrieve data from the employee table to check how the table has been modified:

select * from employee;

The Select command will retrieve the data stored in the table and generate the following output:

The employee table has been modified and it now, contains two new records. This is how you can use the Snowflake Insert command to insert multiple data records in your desired table easily.

Inserting nested data using Snowflake Insert command

The Insert command lets you insert the data fetched by querying a particular table and then inserting the resultant data into your desired Snowflake table.

For example, if you’re working with two tables, namely contractor and employee and you want to insert data from contractor table into the employee table, you can do this easily using the Insert command.

First, use the select statement to retrieve the existing data from your tables using the following commands:

select * from employee;

The Select command will retrieve the entire data stored in the employee table as follows:

select * from contractor;

The Select command will retrieve the entire data stored in the contractor table as follows:

You can now use the Insert command and make use of a nested SQL query to insert data into the employee table by fetching it from the contractor table as follows:

insert into employees(first_name, last_name, workphone, city,postal_code)
  select
    contractor_first,contractor_last,worknum,null,zip_code
  from contractors
  where contains(worknum,'650');

Use the Select statement to retrieve data from the employee table to check how the table has been modified:

select * from employees;

This is how you can use the Snowflake Insert command to add new data records into your desired tables.

Conclusion

This article teaches you how to successfully use the Insert command to add data to your desired Snowflake table. It provides in-depth knowledge about the concepts behind every step to help you understand and implement them efficiently. These methods, however, can be challenging, especially for a beginner & this is where Hevo saves the day.

Hevo Data, a No-code Data Pipeline helps to transfer data from 150+ sources to Snowflake without having to write the code repeatedly. Hevo with its strong integration allows you to not only export & load data but also transform & enrich your data to make it analysis-ready in a jiff.

Want to take Hevo for a spin? Sign up here for a 14-day free trial! and experience the true power of Hevo.

Tell us about your experience of using the Snowflake Insert command! Share your thoughts in the comments section below.