Create a copy of a table within the same database DB2

Try this:

CREATE TABLE SCHEMA.NEW_TB LIKE SCHEMA.OLD_TB;
INSERT INTO SCHEMA.NEW_TB (SELECT * FROM SCHEMA.OLD_TB);

Options that are not copied include:

You have to surround the select part with parenthesis.

CREATE TABLE SCHEMA.NEW_TB AS (
    SELECT *
    FROM SCHEMA.OLD_TB
) WITH NO DATA

Should work. Pay attention to all the things @Gilbert said would not be copied.

I'm assuming DB2 on Linux/Unix/Windows here, since you say DB2 v9.5.

Two steps works fine:

create table bu_x as (select a,b,c,d from x ) WITH no data;

insert into bu_x (a,b,c,d) select select a,b,c,d from x ;

Related