Hi All,

I am new t0 Db2.
I am creating a table from another table as below:

Create table New Like Old

Insert into New select * from old

Is there any better way than doing above.

I am using below query which is not working in DB2

Create table new as select * from old;

Please let me know if there are any better options.

Thanks,
Mrcool

I realize that having to submit two SQL statements (one to clone the table, and another insert the data from the other table) means your life is most likely ruined, but I don't think you can do this with one SQL statement in DB2 (unless there is a new feature I don't know about).

Thanks Marcus. But is there any other approach than what I am doing.

Thanks,
Mrcool

The only other way I know of is to EXPORT the data from first table using IXF format, and then IMPORT into a new table using the REPLACE_CREATE option. This will create the table using the table definition contained in the export file (IXF format only) if the table does not already exist. If you have more than 50,000 rows, I would use the COMMITCOUNT option on the IMPORT.

Once Again Thanks for your inputs Marcus. It is helpfull.

Cheers
Mrcool

With load you could bypass the transaction log.
declare load_cur cursor for select * from old;
load from load_cur of cursor insert into new nonrecoverable;

But that would be 3 Statements. On the other hand it's a lot faster than Import or Insert for large amonut of data.

Caution! Nonrecoverable means that the data is not recoverable in case of restore.
The "create table ... like ..." does not copy any Indexes, Constraints etc.

Cheers
nvk

Simply use the "with data" option"
For example
create table test as (select * from xxxusersxxx) with data

create table test as (select * from xxxusersxxx) with data

was this tested ?? there is a mandatory option : with no data according the syntax diagram - not seen this option before

I was actually using it when I typed the message. So I can say that I've tested it.

Looking into manuals,
DB2 for iSeries(at least V5R4) supports WITH DATA in as-subquery-clause, like...
AS (select-statement) WITH DATA

DB2 10 for z/OS and DB2 9.7 for LUW don't support WITH DATA.

That gives me an error: (DB2 LUW 9.5)

I have checked the syntax diagrams for LUW 9.5 and 9.7 and neither have this option. They only have "WITH NO DATA". I even check DB2 for Z/OS and the syntax does not show it.

Andy