copydb2aix

Create a copy of a table within the same database DB2

Is there an easy way to copy a table to the same database of course with different name. I tried some of these listed below,

db2 "CREATE TABLE SCHEMA.NEW_TB COPY AS SELECT * FROM SCHEMA.OLD_TB WHERE 1 = 2"

db2 "SELECT INTO SCHEMA.NEW_TB FROM SCHEMA.OLD_TB"

db2 "SELECT * FROM SCHEMA.OLD_TB INSERT INTO SCHEMA.NEW_TB"

None of these worked I am using db2 v9.5

Solution

  • 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.