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

User · Accepted Answer

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.

User · Answer

We can copy all columns from one table to another  existing table   INSERT INTO table2  SELECT   FROM table1    Or we can copy only the columns we want to into another  existing table   INSERT INTO table2   column name s    SELECT column name s   FROM table1   or SELECT   INTO BACKUP TABLE1 FROM TABLE1

User · Answer

CREATE TABLE NEW TABLENAME LIKE OLD TABLENAME    Works for DB2 V 9 7

User · Answer

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

User · Answer

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

[copy] Create a copy of a table within the same database DB2

Examples related to copy

Examples related to db2

Examples related to aix