RE: SQL Create Table AS with primary key

Unfortunately the "as result table" syntax only lets you copy column attributes like identity, defaults, implicitly hidden, and row change timestamps.



An alternative is to create a VIEW, then create the table using the LIKE TABLE/VIEW syntax.



CREATET VIEW myLib.ResultV1 (Cola, Colb, ColC, NewD, NewF) as
(
Select t1.ColA, t1.ColB, t1.ColC, t2.ColA, t1.ColD
from myLib.Table1 t1
join myLib.Table2 t2
on t1.ColA = t2.ColA and t1.ColB = t2.ColB
where t1.ColA = 208
and t1.ColR = 'Y'
and t1.ColB in
(
Select t1b.ColB
from myLib.Table1 t1b
where t1b.ColA = 208
and t1b.ColR != 'Y'
and t1b.ColM = 2023
)
)
;

create table myLib.ResultTb (LIKE myLib.ResultV1 , PRIMARY KEY (ColA, ColB) )

Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.

From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of K Crawford
Sent: Tuesday, August 2, 2022 10:52 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL Create Table AS with primary key
Report This Email<https://protection.inkyphishfence.com/report?id=d2luY29mb29kcy9jaHJpcy5oaWViZXJ0QHdpbmNvZm9vZHMuY29tLzQwZDAxM2RkMjRjNzljNjNiYTI5YmJiMDcwNDI1ODI2LzE2NTk0NTkxMjIuMDQ=#key=73a8937c2eb47813957f426d64a377e7>


The Alter Table never crossed my mind. That will work also.

I hoped to do it in the create table to eliminate the second script.

I will use the Alter Table.