CREATE TABLE using SELECT statement

[msalman_ali]

Dear Support Manager,

I am using SQL/400 on iSeries i520, V5R3, OS/400.

I like to create an table using SQL/400 by giving select statement, i.e. I am giving reference that i used to to perform in oracle
SQL> create table ABC as ( select * from xyz) ;
above sql statement working perfectly in oracle provided xyz
tabel exist in oracle environlment.

My question is that is there any way in SQL/400 to create table using select statement.

looking forward for an immediate response.

Regards

Salman

[vinomails]

Re: CREATE TABLE using SELECT statement

Hi Salman,

There is no direct command to do that. But we have to change the session defaults then creating a table is possible.

1. Press F13
2. Choose option 1
3. Go to Select Output and Give option as 3
4. Then it will ask you to enter the file name and library name.
5. Press enter
6. Now give your select command.
7. The table will be created automatically.

Note: Once you done this again go to Session defaults (F13) and change to Select output as 1.

Regards,
Vinothkumar S.

[arrow483]

Re: CREATE TABLE using SELECT statement

How about:
create table ABC as ( select * from xyz) with data;

[tomholden]

Re: CREATE TABLE using SELECT statement

or if you just want the table with no data
create table ABC as ( select * from xyz) definition only

[Mercury]

Re: CREATE TABLE using SELECT statement

or ...

create table ABC LIKE xyz

to create the table ABC with no data.

[msalman_ali]

Re: CREATE TABLE using SELECT statement

Thanks all of above that replied my following options working perfectly

a) create table ABC as ( select * from xyz) with data
b) create table ABC as ( select * from xyz) definition only
c) create table ABC LIKE xyz

Regards
Salman

[williamhphillip]

Re: CREATE TABLE using SELECT statement

Thank you sir that was the answer I was looking for.