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

Repulsive

Why are You using 1 = 2 condition for this? – Commutative

Oh, ok, because You don't want to copy data, just schema. Sorry for bothering You ;) – Commutative

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.

Anticosti

Your WHERE condition is a little odd. If you're using the false predicate to have DB2 not copy data, you can do it as I have here with WITH NO DATA to only copy the structure. Or you can just do a CREATE TABLE SCHEMA.NEW_TB LIKE SCHEMA.OLD_TB. – Anticosti

Why "WITH NO DATA" Didn't he wanted to copy the content too? How to create the table AND do inserts? – Troopship

@ThorstenNiehues As much as it makes no sense why, there is no WITH DATA or WITH ALL DATA option, and you also cannot leave out the WITH clause. So we have to use WITH NO DATA (or the LIKE version) and then pump in the data using a separate INSERT ... SELECT query. – Branscum

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

Ascendancy

INSERT INTO SCHEMA.NEW_TB(column names) SELECT column,names FROM SCHEMA.OLD_TB; some table had issues with column names not in the correct place and this helped to solve that issue. – Pinfeather

This doesn't copy primary keys and indexs. Any idea how to do this? – Statistics

Answer 3

CREATE TABLE NEW_TABLENAME LIKE OLD_TABLENAME;

Works for DB2 V 9.7

Eustache

It DOES NOT copy DATA. – Shaunshauna

The question is actually not clear with regard to this requirement. The accepted answer does not copy data either. – Statistics

Answer 4

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 a,b,c,d from x ;

Skat

Can you confirm that select select in the second step is correct and intended? – Gossipry

INSERT INTO XXX.YYY (SELECT * FROM ZZZ.TTT) worked for me on DB2 7 – Garnierite

Answer 5

CREATE TABLE SCHEMA.NEW_TB COPY AS 
(SELECT * FROM  SCHEMA.OLD_TB WHERE 1 = 2)
WITH DATA

Works for me all the time

Onder

WHERE 1 = 2 and WITH DATA cancel each other out, being the same as a query without the WHERE clause but with WITH NO DATA. – Headless

Answer 6

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

Criseldacrisey