[copy] 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

This question is related to copy db2 aix

The answer is


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

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


Examples related to copy

Copying files to a container with Docker Compose Copy filtered data to another sheet using VBA Copy output of a JavaScript variable to the clipboard Dockerfile copy keep subdirectory structure Using a batch to copy from network drive to C: or D: drive Copying HTML code in Google Chrome's inspect element What is the difference between `sorted(list)` vs `list.sort()`? How to export all data from table to an insertable sql format? scp copy directory to another server with private key auth How to properly -filter multiple strings in a PowerShell copy script

Examples related to db2

DB2 SQL error sqlcode=-104 sqlstate=42601 DB2 SQL error: SQLCODE: -206, SQLSTATE: 42703 How to update multiple columns in single update statement in DB2 Difference between CLOB and BLOB from DB2 and Oracle Perspective? How to AUTO_INCREMENT in db2? DB2 Timestamp select statement insert multiple rows into DB2 database Create a copy of a table within the same database DB2 determine DB2 text string length DB2 Date format

Examples related to aix

Changing an AIX password via script? Find files in created between a date range How to ignore conflicts in rpm installs Create a copy of a table within the same database DB2 ORA-00060: deadlock detected while waiting for resource How to get the command line args passed to a running process on unix/linux systems? How to mkdir only if a directory does not already exist? What is the unix command to see how much disk space there is and how much is remaining?