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;

只需指定目标临时表中与select语句中的列列表匹配的列。
是的，您必须指定包括数据类型在内的所有列名。

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;

文件参考：https://docs.snowflake.com/en/sql-reference/sql/insert.html#optional-参数