Re: Create SQL table LIKE another table, and add a new column

On 21-Dec-2016 12:26 -0700, Dan wrote:

(This is based on a thread I started last week, "Add a unique key
field to view without redefining table".)

So, I'm attempting to create a new table by way of using LIKE:

create table newtable
like oldtable
, UniqueID BigInt GENERATED ALWAYS AS IDENTITY

It's not liking the comma, giving SQL0104:
Token , was not valid. Valid tokens: IN ON NOT DATA LONG OBID UNIT
WITH INDEX VALUE APPEND RCDFMT.

I've been reading the CREATE TABLE section in the SQL reference, and
coming up blank. I've searched online, but not finding relevant
results.

The LIKE syntax used must be the parenthetical version; i.e. written inside parentheses, to effect anything other than adding the INCLUDING and EXCLUDING clauses of the copy-options.

For example [tested on v7r3, but for completion only; i.e. no further verification of file nor field attributes, nor functionality]:

create table onecol (c char)
;
create table twocol_id
( LIKE onecol
, UniqueID BigInt GENERATED ALWAYS AS IDENTITY
, PRIMARY KEY (UniqueID)
)
;

For a past discussion giving a doc link, see:

[Create table LIKE another including Primary key...](http://archive.midrange.com/midrange-l/201606/msg00370.html)
"The LIKE clause has two distinct usages, depending on where are, or are
not, parentheses included in the CREATE TABLE statement; i.e. there are two
variants for syntax, for the use of the "LIKE <view|table-name>
<copy-options>" clause. Use the following syntax, rather than that which
is shown in the OP:

CREATE TABLE mytbl2
( LIKE mytbl1
, PRIMARY KEY (mykey)
)

…

Doc reference to clarify the syntax:
[IBM i -> IBM i 7.1 -> Database -> Reference -> SQL reference -> Statements -> CREATE TABLE](http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/db2/rbafzhctabl.htm)

…"