467,122 Members | 1,250 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,122 developers. It's quick & easy.

Is there an easy to copy a table: structure and data?

For testing purposes I propose to add a schema (testing, how original)
and would like to copy some of my live tables to it, both structure and
data. I know I could use something like dump/restore for the data and
Quest's (BTW this a very nice product) 'create like' function. That
would work, but it seems a lot of work for something DB2 should know how
to do.

Nov 12 '05 #1
  • viewed: 38040
Share:
4 Replies
db2look with -e -t can give you the table CREATE statements.

The DB2 Control Center will allow you to copy a table and its contents.

You can also do a CREATE ... LIKE to copy the table structure (but not
the rows):

create table emp2 like employee

you could then do an insert:

insert into emp2 (select * from employee)

Robert Stearns wrote:
For testing purposes I propose to add a schema (testing, how original)
and would like to copy some of my live tables to it, both structure and
data. I know I could use something like dump/restore for the data and
Quest's (BTW this a very nice product) 'create like' function. That
would work, but it seems a lot of work for something DB2 should know how
to do.


Nov 12 '05 #2
db2look with -e -t can give you the table CREATE statements.

The DB2 Control Center will allow you to copy a table and its contents.

You can also do a CREATE ... LIKE to copy the table structure (but not
the rows):

create table emp2 like employee

you could then do an insert:

insert into emp2 (select * from employee)

Robert Stearns wrote:
For testing purposes I propose to add a schema (testing, how original)
and would like to copy some of my live tables to it, both structure and
data. I know I could use something like dump/restore for the data and
Quest's (BTW this a very nice product) 'create like' function. That
would work, but it seems a lot of work for something DB2 should know how
to do.


Nov 12 '05 #3
Blair Adamache wrote:
db2look with -e -t can give you the table CREATE statements.

The DB2 Control Center will allow you to copy a table and its contents.

You can also do a CREATE ... LIKE to copy the table structure (but not
the rows):

create table emp2 like employee

you could then do an insert:

insert into emp2 (select * from employee)

Robert Stearns wrote:
For testing purposes I propose to add a schema (testing, how original)
and would like to copy some of my live tables to it, both structure and
data. I know I could use something like dump/restore for the data and
Quest's (BTW this a very nice product) 'create like' function. That
would work, but it seems a lot of work for something DB2 should know how
to do.


Another alternative would be to take an EXPORT to IXF format, and then do an
IMPORT with the "REPLACE_CREATE" option. This would work across databases
as well. It will only give you the basic table structure and data, and
you'd need to use db2look to generate the statements to add indexes etc.

HTH

Phil
Nov 12 '05 #4
Blair Adamache wrote:
db2look with -e -t can give you the table CREATE statements.

The DB2 Control Center will allow you to copy a table and its contents.

You can also do a CREATE ... LIKE to copy the table structure (but not
the rows):

create table emp2 like employee

you could then do an insert:

insert into emp2 (select * from employee)

Robert Stearns wrote:
For testing purposes I propose to add a schema (testing, how original)
and would like to copy some of my live tables to it, both structure and
data. I know I could use something like dump/restore for the data and
Quest's (BTW this a very nice product) 'create like' function. That
would work, but it seems a lot of work for something DB2 should know how
to do.


Another alternative would be to take an EXPORT to IXF format, and then do an
IMPORT with the "REPLACE_CREATE" option. This would work across databases
as well. It will only give you the basic table structure and data, and
you'd need to use db2look to generate the statements to add indexes etc.

HTH

Phil
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Stewart Allen | last post: by
3 posts views Thread by Leo Nunez | last post: by
2 posts views Thread by charles-brewster@ntlworld.com | last post: by
9 posts views Thread by sck10 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.