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 DATACreating 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 DATAIncluding 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 DATASpecifying 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 WHERE clause:
CREATE TABLE USERCOPY AS (
SELECT * FROM USER WHERE last_login IS NOT NULL
) WITH DATAGetting 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 XYLTD database:
db2look -d XYLTD -z ERP -t USER -eReference
Db2 documentation: