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/supp
ort/knowledgecenter/en/ssw_ibm_i_71/db2/rbafzhctabl.htm)

…"

--
Regards, Chuck


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD