Primary Key - Create table (select)

UDA

Primary Key - Create table (select)

Is it possible to set a PRIMARY KEY when I'm creating a table from a select statement?

Ex.:

CREATE TABLE TEST
AS (SELECT VAR1, VAR2, VAR3
FROM TABLE) WITH DATA;

Where VAR1 should be the PRIMARY KEY.

Tks,

Marc
2 REPLIES
Enthusiast

Re: Primary Key - Create table (select)

I don't believe that the syntax allows that, since the PRIMARY KEY specification is done along with specifying the columns of the table.

You can specify a UNIQUE PRIMARY INDEX though by doing this:

CREATE TABLE TEST
AS (SELECT VAR1, VAR2, VAR3
FROM TABLE) WITH DATA
UNIQUE PRIMARY INDEX(VAR1);

Hope that helps.

Barry

Re: Primary Key - Create table (select)

Please use command below

CREATE TABLE [newtable] LIKE [oldtable]

it will automatically create table with the same primary key as the old table.