Create Table using AS in DB2 for i

Create Table using AS in DB2 for i

We can create a table from the result of a SELECT statement. We can use CREATE TABLE AS statement to create such type of tables in DB2.

We can put all of the SELECT expression in CREATE TABLE AS statement.

We can also include all of the data from the table/tables that we are selecting from.

Example 1:

Create a table named PF2 that includes all the column definitions from the table PF1.

CREATE TABLE PF2 AS 
        (SELECT * FROM PF1) 
        WITH NO DATA

Here, we created table named PF2 same as PF1 by using AS keyword and selected all the columns from the PF1 table. Also, we must need to specify whether we need data or not from the selected table into the new table that is to be created.

Example 2:

Create a table named PF2 that includes all the column definitions from the table PF1 and all the data from the table PF1.

CREATE TABLE PF2 AS 
        (SELECT * FROM PF1) 
        WITH DATA

Example 3:

Create a table named PF2 that includes subset of column definitions from the table PF1 and no data from the table PF1.

CREATE TABLE PF2 AS 
        (SELECT Empid, empname FROM PF1) 
        WITH NO DATA

Example 4:

Create a table named PF2 that includes subset of column definitions from the table PF1 and all the data from the table PF1.

CREATE TABLE PF2 AS 
        (SELECT Empid, empname FROM PF1) 
        WITH DATA

Example 5:

Create a table named PF2 that includes subset of column definitions from the table PF1 and the data from the table PF1 where manager id is 1.

CREATE TABLE PF2 AS 
        (SELECT Empid, empname FROM PF1 WHERE MANAGERID = 1) 
        WITH DATA

Additional information on using CREATE TABLE AS statement in DB2 for i

If the specified table or view contains an identity column then we must specify the option INCLUDING IDENTITY on the CREATE TABLE statement if we want the identity column to be there in the new table that is going to be created as a result of the CREATE TABLE AS statement. The default behavior for CREATE TABLE is EXCLUDING IDENTITY.

CREATE TABLE PF2 AS 
        (SELECT Empid, empname FROM PF1) 
        WITH NO DATA INCLUDING IDENTITY

If PF1 would have the identity column then we can also copy the identity column using the INCLUDING IDENTITY option in the CREATE TABLE AS statement.

The WITH NO DATA clause indicates that the column definitions are to be copied without the data

The WITH DATA clause indicates that the column definitions are to be copied with the data