Add multiple columns to Snowflake table, simply explained

Adding multiple columns to a table in Snowflake is a common and easy task to undertake by using the alter table command, here is the simplest example of how to add multiple columns to a table:

alter table table_name 
add 
	new_column_1 number,
	new_column_2 date

Add multiple columns in Snowflake if table exists already

We can build upon the simple example we showed previously by adding an if exists constraint, which checks first if the table exists before adding the columns to the table. This is helpful as it stops potential errors being returned.

alter table if exists table_name
add 
	new_column_1 number,
	new_column_2 date

Adding multiple columns to Snowflake table with constrains

We always need to define the datatype of the column that we are adding, which we have shown in each example so far, but we could also apply other constraints to the columns that we are adding. These constraints could be:

• unique
• primary key
• foreign key
• not null
• maximum length
• default value

In this example I will show how to add the common not null and default constraints to the new columns

alter table if exists table_name 
	add new_column_1 number default 0,
	add new_column_1 date not null,

Adding new columns in Snowflake, things to watch out for

When adding new columns, there are two things to keep in mind:

• The new column names must not be currently used in the table
• Objects (such as view definitions) that select all columns from your altered table will now fetch the new columns, if this is not wanted then you will have to go and edit these objects manually

Related articles

Editing tables

Drop one or more columns from Snowflake table
Rename Snowflake column
Add column to Snowflake table

References

Snowflake alter table documentation