How to create a copy of a table. The copy can contain data, a selective portion of the data, or it can be empty.
Creating an empty copy
Creating an empty copy of a table:
CREATE TABLE USERCOPY AS ( SELECT * FROM USER ) WITH NO DATA
Creating a copy with data
To copy a table and all the rows it contains, we will specify
WITH DATA instead of
WITH NO DATA.
CREATE TABLE USERCOPY AS ( SELECT * FROM USER ) WITH DATA
Including a portion of data
If we’re going to run some tests, we’ll probably want to copy a limited number of rows, because it’s faster. To copy 100 rows:
CREATE TABLE USERCOPY AS ( SELECT * FROM USER ORDER BY id LIMIT 100 ) WITH DATA
ORDER BY guarantees that, if we run the command multiple times, we’ll always copy the same tows.
To only copy data that match certain conditions, we can use the
CREATE TABLE USERCOPY AS ( SELECT * FROM USER WHERE last_login IS NOT NULL ) WITH DATA
Getting the CREATE TABLE statement
Another option is to get the
CREATE TABLE statement to use to recreate an identical table. We can use this command on another Db2 server, or we can save it for later.
This is not something that we can do in SQL. Instead, we’ll use a tool that comes with Db2, called
db2look. Suppose the table
USER is located in the schema
ERP, in the
db2look -d XYLTD -z ERP -t USER -e