CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
KDD
KDD

Reputation: 1

Is there a way to make a temp table in Snowflake via SQL without having to write the columns in everytime? WITH, INSERT INTO

insert into temp
select  code, 
case when code = 'S' then 1
when code = 'U' then 0
when code = 'R' then 4 
end 
from "table" r

temp table error-how do you make a temp table in snowflake without having to note each column

Upvotes: 0

Views: 5428

Answers (2)

FKayani
FKayani

Reputation: 1021

You only need to specify the columns of the target temp table matching to the list of columns list in the select statement.

Yes, you have to specify all column name including data types.

create table "table" ( code varchar);

insert into "table" values ('S');
insert into "table" values ('U');
insert into "table" values ('R');

select  code, 
case when code = 'S' then 1
when code = 'U' then 0
when code = 'R' then 4 
end 
from "table" r;

Create temp table temp as
select  code, 
case when code = 'S' then 1
when code = 'U' then 0
when code = 'R' then 4 
end 
from "table" r; --SQL compilation error: Missing column specification


Create temp table temp (code varchar, code_no int) as
select  code, 
case when code = 'S' then 1
when code = 'U' then 0
when code = 'R' then 4 
end as code_no
from "table" r;

select * from temp;

Document Reference: https://docs.snowflake.com/en/sql-reference/sql/insert.html#optional-parameters

Upvotes: 0

Mike Gohl
Mike Gohl

Reputation: 737

Create temp table temp as
select  code, 
case when code = 'S' then 1
when code = 'U' then 0
when code = 'R' then 4 
end 
from "table" r;

Upvotes: 1

Related Questions