
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.
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