How to implement an Insert which has a subquery in Snowflake?

Snowflake supports insert statements with only one select phrase in it. You cannot embed a select in an Insert command. For instance, if you  want to insert into a table and choose a few values from other tables, like below,

insert into Employee values('1', (select max(b) +1 from Employee), 'val3', 'val4');

it will fail in snowflake with the below error:

SQL compilation error: Invalid expression xxxx in VALUES clause

The Solution:

You will need to rewrite such queries in snowflake using select and/or joins,

insert into Employee select '1', max(b) +1, 'val3', 'val4' from Employee;

If you need to pull data from a different table, you will need to use joins to write proper select insert query as Snowflake doesn't support embeds:

insert into emp (id,first_name,last_name,city,postal_code,ph)
  select a.id,a.first_name,a.last_name,a.city,a.postal_code,b.ph
  from emp_addr a
  inner join emp_ph b on a.id = b.id;

nVector

posted on 01 Apr 19