Create a copy of a table within the same database DB2

Question

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

Answer 1

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.

Answer 2

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:

• Check constraints
• Column default values
• Column comments
• Foreign keys
• Logged and compact option on BLOB columns
• Distinct types