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

Oh, ok, because You don't want to copy data, just schema. Sorry for bothering You ;) — Line, Oct 28 '15 at 10:12

score 44 · Answer 1 · answered Jul 10 '12 at 15:47

44

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

answered Jul 10 '12 at 15:47

Gilbert Le Blanc

50,182
6
67
111

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. – Yogamurthy Jan 02 '19 at 18:38
This doesn't copy primary keys and indexs. Any idea how to do this? – not2savvy Apr 11 '19 at 09:03

score 43 · Accepted Answer · answered Jul 11 '12 at 06:26

43

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.

answered Jul 11 '12 at 06:26

bhamby

15,112
1
45
66

2

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`. – bhamby Jul 11 '12 at 06:29
1

Why "WITH NO DATA" Didn't he wanted to copy the content too? How to create the table AND do inserts? – Thorsten Niehues Jun 03 '16 at 07:37
2

@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. – ADTC Jul 13 '16 at 04:46

score 4 · Answer 3 · answered Nov 24 '14 at 10:40

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

answered Nov 24 '14 at 10:40

Maarten

41
2

2

Can you confirm that `select select` in the second step is correct and intended? – EWit Nov 24 '14 at 10:52
1

`INSERT INTO XXX.YYY (SELECT * FROM ZZZ.TTT)` worked for me on DB2 7 – ATorras Jun 09 '15 at 10:53

score 3 · Answer 4 · edited Oct 08 '14 at 13:28

3

CREATE TABLE NEW_TABLENAME LIKE OLD_TABLENAME;

Works for DB2 V 9.7

edited Oct 08 '14 at 13:28

xlecoustillier

16,183
14
60
85

answered Oct 08 '14 at 13:08

Rbk528

47
2

1

It DOES NOT copy DATA. – KMetin Oct 27 '14 at 13:32
3

The question is actually not clear with regard to this requirement. The accepted answer does not copy data either. – not2savvy Apr 10 '19 at 08:50

score 1 · Answer 5 · edited May 19 '23 at 15:44

1

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

Works for me all the time

edited May 19 '23 at 15:44

Tyler2P

2,324
26
22
31

answered May 18 '23 at 19:50

Hassan Farooqi

11
2

score 0 · Answer 6 · answered Sep 10 '15 at 13:39

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

Create a copy of a table within the same database DB2

6 Answers6