Identity column in Snowflake

0
votes

A simple question in Snowflake. I created a table with an identity column.

create table etl_test 
(id int identity,
name string);

And tried to insert data into it. In my SQL Server world, I just need to provide the value for the name field.

insert into etl_test values('test');

I tried the same in Snowflake but I am getting an error

SQL compilation error: Insert value list does not match column list expecting 2 but got 1

How can I make sure that the value 'test' is inserted with id = 1?

2
snowflake-cloud-data-platform

2 Answers

0
votes

You need to include the column name(s) you are using if you aren't inserting into all the columns:

insert into etl_test (name) values('test');
0
votes

"Blind inserts" are antipattern regardless of RDBMS.

In your scenario, you need to specify column list and either remove id from column list or provide a DEFAULT for it.

INSERT INTO etl_test(id, name) VALUES (DEFAULT, 'test');