How to Insert Data in Snowflake

In Snowflake, data insertion is performed to insert data into a newly created table or existing table. For inserting data, we will use the columns list. In this blog, we will learn how to insert data with and without columns.

Snowflake Insertion operation updates the table by inserting multiple rows into a table. Values inserted into every column in the table can be externally defined or the results of the query. We can insert the data with parameters and without parameters. This “How to Insert Data in Snowflake” blog discusses inserting data through different techniques. 

Insert Data in Snowflake

In Snowflake, data insertion is a very easy process. By specifying the values, we can insert the data into Snowflake tables. The important aspect is we have to pass every value in order. For example, if we have 15 columns, we have to specify 15 columns.

Insert into sessions1 values (2, ‘2022-03-10 10:15:19.400’, ‘2022-04-10 10:35:19.400’, 1);

Specifying Columns list before the “VALUES” is highly recommended:

Insert into sessions1 (id, start_date, end_date, category)
Values (13, ‘2022-03-09 16:15:19.500’, ‘2022-03-11 17:53:57.753’, 2);

After specifying the column list, we don’t need to remember the column order as specified in the Snowflake table. 

Insert into sessions1 (category, id, start_date, end_date)
values(2,3, ‘2022-06-08 15:05:15.500’, ‘2022:08:10  17:07:53.600’);

If we include the Columns list, we can have the following advantages:

  • We don’t need to define the values for every column.
  • If we have various columns, it is easy to match the value to the target column when we view it in the statement, instead of looking at the table definition.
  • The “INSERT” statements without the column lists are disproved when the column is removed or added from a table. We have to change the query to impact the deleted or new column in order to work again. 
Preparing for Snowflake Interview? Here’s Top Snowflake Interview Questions and Answers

For example, if you have various columns, we have to specify only some columns:

Insert into sessions1(start_date, id) values (5, ‘2022-05-03 15:17:35.600’);

MindMajix YouTube Channel

Inserting Multiple Rows

Using a Single INSERT statement, we can insert multiple rows by enclosing multiple set of values enveloped in the parenthesis:

Insert into sessions1 (category, start_date, end_date, id)
values
(7, ‘2022-05-03  16:10:25.500’, ‘2022-03-04 17:15:45.553’, 5),
(6, ‘2022-03-05 17:20:25.300’, ‘2022-09-05 18:25:10.500’, 6),
(8, ‘2022-05-07 19:15:20.200’, ‘2022-07-09 21:05:09.600’, 7);

We can also utilise the CREATE TABLE” with the SELECT command for copying the data from the existing table.

---excluding the column list specification

Create table sessions_dm_2 as
Select *
from sessions1
Where id <=6

Inserting JSON Values

If we have to insert the data into the JSON column, just enclose valid JSON in a quoted string

Insert into sessions1(dates) values( ‘ {“start_date”: “2022-05-07 17:10:20.600”, “end_date”: “2022-08-09 16:12:25.180”}’);

Conclusion

As Snowflake is data storage and process platform, hence data insertion is an essential process. We hope this blog will provide you with the necessary knowledge on data insertion.

Snowflake Related Articles


▶  Snowflake vs Redshift
▶  Snowflake vs BigQuery
▶  Snowflake vs Databricks
▶  Snowflake vs Azure
▶  Snowflake vs Hadoop
▶  Snowflake Time Travel

Job Support Program

Online Work Support for your on-job roles.

jobservice

Our work-support plans provide precise options as per your project tasks. Whether you are a newbie or an experienced professional seeking assistance in completing project tasks, we are here with the following plans to meet your custom needs:

  • Pay Per Hour
  • Pay Per Week
  • Monthly
Learn MoreGet Job Support
Course Schedule
NameDates
Snowflake TrainingDec 14 to Dec 29View Details
Snowflake TrainingDec 17 to Jan 01View Details
Snowflake TrainingDec 21 to Jan 05View Details
Snowflake TrainingDec 24 to Jan 08View Details
Last updated: 04 Apr 2023
About Author

Viswanath is a passionate content writer of Mindmajix. He has expertise in Trending Domains like Data Science, Artificial Intelligence, Machine Learning, Blockchain, etc. His articles help the learners to get insights about the Domain. You can reach him on Linkedin

read less
  1. Share:
Snowflake Articles