CODEWITHSUNDEEP
snowflake-cloud-data-platform
Vinod Jayachandran
Vinod Jayachandran

Reputation: 3898

Snowflake Create table with subset of columns populated from Select statement

On Snowflake is there a way to create table with subset of columns populated from Select statement from other tables ?

From the examples I see on documentation, we can either populate all the columns through Select from other tables or populate none of the columns.

Upvotes: 4

Views: 4472

Answers (2)

Stevko
Stevko

Reputation: 4495

The hive mind fed me this solution today. It brings all the columns except one (or more).

create table t1 as 
select * except( field ) from t2;

Should be good on snowflake.

Upvotes: 0

bradleykirwan
bradleykirwan

Reputation: 168

One way would be to select NULL cast to the desired data type, with an alias as part of the SELECT statement.

CREATE TABLE t1 AS
SELECT
    col1,
    col2,
    NULL::timestamp_ntz AS col3,
    NULL::number AS col4
FROM t2;

This example would create a table t1 with four columns, two of which come from t2.

Upvotes: 4

Related Questions