Db2: Copying a table

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

Specifying 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 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 XYLTD database:

db2look -d XYLTD -z ERP -t USER -e

Reference

Db2 documentation: