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
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:
CREATE TABLE NEW_TABLENAME LIKE OLD_TABLENAME;
Works for DB2 V 9.7
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 ;
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
Source: Stackoverflow.com