how to create new table as old table and aditionally two columns in new table?

General
Enthusiast

how to create new table as old table and aditionally two columns in new table?

 
2 REPLIES
Enthusiast

Re: how to create new table as old table and aditionally two columns in new table?


---New table with data

dbname.test123 is an existing table with 2 columns(col1,col2)

create table dbname.new_test123(col1,col2,col3,col4) as (select col1,col2,NULL,NULL from dbname.test123) with data;

---new table without data

create table dbname.new_test123(col1,col2,col3,col4) as (select col1,col2,NULL,NULL from dbname.test123) with no data;

Thanks

Enthusiast

Re: how to create new table as old table and aditionally two columns in new table?

You can create table as mentioned above  but with the exception that the PI would become the first column only and may not reflect the original table PI. you can use something like this.

Create table dbname.new_table as dbname.old_table with data;

alter table dbname.new_table add new_col1 varchar(10), add new_col2 varchar(10) ;

This way the PI would be the same.

Alternately you can take the definition of the old table, add the 2 new columns, create a new table and do an insert sel with NULLs/defaults for the new columns.