CODEWITHSUNDEEP
snowflake-cloud-data-platformnullablecreate-table
Rusty75
Rusty75

Reputation: 517

Snowflake create table like another table but set all columns to nullable

as the subject line says, I would like to automatically create tables based on a list of tables in an excel, but if these are tables ending with _ERR, I want to strip any non-null limitations (a flaw in the old system).

Is there a way to do that? Just the actual statement, the rest of the logic is covered.

So something like :

CREATE TABLE … LIKE ... SET NULLABLE

would be nice.

Thank you!

Upvotes: 0

Views: 750

Answers (3)

Scott Redding
Scott Redding

Reputation: 86

CREATE [ OR REPLACE ] TABLE <table_name> LIKE <source_table>

then use alter to remove the NULL.

ALTER TABLE t1 ALTER COLUMN c1 DROP NOT NULL;

Upvotes: 1

PIG
PIG

Reputation: 602

you can create you statements by awk

awk  'FS="_" { if($NF=="ERR") print "create table like",$0 , "as nullable;"; else print "create table like",$0} ' file

o/p
    create table like table_1
    create table like table_2
    create table like table_3_ERR as nullable;
    create table like table_4

where i guess file includes below table names.

cat file
table_1
table_2
table_3_ERR
table_4

Upvotes: 0

Jacobm001
Jacobm001

Reputation: 4539

This is not a thing you can do natively with Snowflake's SQL variant. You'll need to script this out yourself.

Upvotes: 1

Related Questions