Duplicating an Entire Table or a Subset of a Table Using SQL
March 23, 2011 Skip Marchesani
|
In my previous article Duplicating CPFY Function Using SQL, I presented three questions that are frequently asked regarding SQL that are interrelated:
I said the answer to all three questions was a qualified YES and introduced the concept of using the INSERT INTO and CREATE TABLE SQL statements along with a subselect to copy data from one or more source tables to a target table or duplicate an existing table. That article had a detailed discussion on how to use the INSERT INTO statement to do the copy and deferred the discussion of the CREATE TABLE statement to this article. To quickly review, the INSERT INTO statement with a subselect allows the selection and copying of columns from one or more source tables and the insertion of the resulting rows into an existing target table. The CREATE TABLE statement with a subselect allows the creation of a target table on the fly and the selection and copying of columns from one or more source tables and the insertion of the resulting row into the newly created target table. It can also used to duplicate a table similar to the function found in the native CRTDUPOBJ command, and also allows a field reference file to be used as the source for column definitions when a table is created. NOTE: A subselect is a SELECT statement imbedded within another SQL statement such as SELECT, UPDATE, DELETE, INSERT INTO, or CREATE TABLE; and up to 256 subselects can be imbedded in a single SQL statement. In the second example from my previous article, the following INSERT INTO statement used two subselects to select a subset of columns and rows from two different source tables called EMP and DEP, and then insert the resulting rows into an existing target table called EMPNAME2. The source tables EMP and DEP are shown after the INSERT INTO statement and are followed by the resulting EMPNAME2 table.
INSERT INTO empname2 (number, name, dept, dptnam)
SELECT nbr, nam, dpt,
(SELECT dnm
FROM dep b
WHERE a.dpt = b.dpt)
AS dptnam
FROM emp a
WHERE dpt IN(911, 977)
EMP Table
Nbr Name Cls Sex Dpt Sal
20 Heikki 2 M 901 6000
10 Ed 5 M 911 7000
50 Marcela 3 F 911 7500
40 Mike 4 M 977 6500
30 John 5 M 977 3200
60 Frank 2 M 990 6500
DEP Table
Dpt Dnm
901 Accounts
977 Manufact
911 Sales
907 Spares
After executing the INSERT INTO statement, EMPNAME2 contains the following rows. EMPNAME2 Table Number Name Dept Dptnam 10 Ed 911 Sales 50 Marcela 911 Sales 40 Mike 977 Manufact 30 John 977 Manufact If we compare this INSERT INTO example to CPYF, it is the equivalent of coping and combining rows from two source tables using a selection criteria and inserting the resulting rows into an existing table, using the *MAP and *DROP record format field mapping options. In this example the target table was empty and had no rows prior to the copy. However, this technique will also work if the target table is not empty and already contains rows prior to the copy. NOTE: When using the INSERT INTO statement to copy data from one or more source tables to a target table, any rules in effect for the target table (unique key, referential, or check constraints, triggers, etc) will be in effect and enforced unless they are disabled or turned off. In this example, the table EMPNAME2 already existed prior to copying the four rows into it. Let’s consider the scenario where this table did not already exist. CPYF provides the option of creating the target table on the fly by using the CRTFILE(*YES) parameter. Can SQL do the same thing? The answer is YES, but the CREATE TABLE statement with subselect is used in place of the INSERT INTO statement with subselect. To create EMPNAME2 as part of the copy process, the INSERT INTO statement is replaced with the CREATE TABLE statement. The subselect remains the same, and some additional SQL syntax is required for the CREATE TABLE statement as shown below.
CREATE TABLE empname2 AS
(SELECT nbr, nam, dpt,
(SELECT dnm
FROM dep b
WHERE a.dpt = b.dpt)
AS dptnam
FROM emp a
WHERE dpt IN(911, 977))
WITH DATA
By executing this CREATE TABLE statement, the table EMPNAME2 is created containing the same rows as shown in the illustration of EMPAME2 above. Following are the two forms of the CREATE TABLE statement that can be used to duplicate an entire table, a subset of a table, or select data from one or more source tables and insert the resulting rows into the target table.
CREATE TABLE target_table_name AS
(SELECT statement for subselect)
WITH DATA
WITH NO DATA
CREATE TABLE target_table_name LIKE source_table-name
The first form of the CREATE TABLE statement with the AS clause and subselect is used to duplicate an entire table, a subset of a table, or select data from one or more source tables and insert the resulting rows into the target table. The source table or tables and any data/row selection criteria are defined by the subselect. Using this form of the create table statement is an implicit definition of the target table that includes all columns in the column list of the SELECT statement for the subselect, including the following associated column attributes from the source table.
The following column attributes are not implicitly included from the source table.
However, these attributes may be explicitly included using the CREATE TABLE copy options. For detailed information, please see Copy Options in the section for CREATE TABLE beginning on page 915 of theV7R1 SQL Reference, which can be found in the IBM i Information Center here. When the AS clause and subselect is used, the WITH DATA or WITH NO DATA clause is required to end the CREATE TABLE statement. WITH DATA specifies that the attributes of the columns in the newly created target table will be defined by the subselect, and the target table will be populated with the rows as a result of executing the subselect. This would be the equivalent of the DATA(*YES) parameter when using the CRTDUPOBJ command. WITH NO DATA specifies that the newly created target table will have no rows and the subselect will only be used to define the attributes of the columns in the newly created target table. This would be the equivalent of the DATA(*NO) parameter in the CRDUPOBJ command. This form of the CREATE TABLE also provides support for a field reference file in SQL. In this case the field reference file would be used as the source table in the subselect and the CREATE TABLE statement would most likely be ended by using the WITH NO DATA clause. The second form of the CREATE TABLE statement with the LIKE clause and source table name is used to duplicate a single source table in its entirety, but the resulting target table will have no rows in it. Using this form of the create table statement is an implicit definition of all the columns from the source table in the newly created target table, including the following associated column attributes from the source table.
If the LIKE clause and source table name immediately follows the target table name and is NOT enclosed in parenthesis, the following column attributes are also implicitly included from the source table.
If the LIKE clause and source table name immediately follows the target table name and IS enclosed in parenthesis, the following column attributes are not implicitly included from the source table. However, except for column headings and text and null capability, they may be explicitly included using the CREATE TABLE copy options. For detailed information, please see Copy Options in the section for CREATE TABLE in V7R1 SQL Reference here. Also, a detailed discussion of the use of CREATE TABLE copy options with the LIKE clause can be found in this article. For both forms of the CREATE TABLE statement, if a source table has not been created using SQL, any column attributes not supported by SQL are dropped such as those from the DDS key words for validity checking and editing. The implicit definition of the target table does not include any optional attributes from the source table such as primary key, foreign, key, or triggers. The first form of the CREATE TABLE statement with the AS clause and subselect can be used to produce the same results as the second form of the CREATE TABLE statement with the LIKE clause and source table name. Take the following syntax, which uses the AS clause and subselect:
CREATE TABLE target_table_name AS
(SELECT * FROM source_table_name)
WITH NO DATA
This command will create a target table identical to the following syntax, which uses the LIKE clause and source table name. CREATE TABLE target_table_name LIKE source_table_name To summarize, in this and my previous article, we explored the following three questions:
The answer to all three questions was a qualified YES and introduced the concept of using the INSERT INTO and CREATE TABLE SQL statements to copy or duplicate a table. The INSERT INTO statement with a subselect allows the selection and copying of one or more columns from one or more source tables and the insertion of the resulting rows into an existing target table. The CREATE TABLE statement with a subselect allows the creation of a target table on the fly and the selection and copying of one or more columns from one or more source tables and the insertion of the resulting row into the newly created target table. The CREATE TABLE statement can also be used to duplicate a table similar to the function found in the native CRTDUPOBJ command, and also allows a field reference file to be used as the source for column definitions when the target table is created. Skip Marchesani retired from IBM after 30 years and decided to pursue a career as a consultant in the IBM i marketplace. He spent much of his IBM career working with the Rochester Development Lab on projects for S/38 and AS/400, and was involved with the development of the AS/400. Skip was part of the team that taught early AS/400 education to customers and IBM lab sites worldwide. Now recognized as an industry expert on DB2 for i (a.k.a. DB2/400), and author of the book “DB2/400: The New AS/400 Database,” Skip specializes in providing customized education for any area of the IBM i and AS/400, does database design and design reviews, and general IBM i, iSeries, and AS/400 consulting. He has been a speaker for user groups, technical conferences, and IBM i, iSeries, and AS/400 audiences around the world, and has received COMMON’s Distinguished Service Award. Skip Recently moved to Vermont and is now president of the Vermont Mid Range User Group (VTMUG). Send your questions or comments for Skip to Ted Holt via the IT Jungle Contact page. RELATED STORIES Duplicating CPFY Function Using SQL Counterintuitive Table Creation
|
Hello Skip.
Please could you assist as you said the AS token does not inherit the field rules:
File1
CREATE TABLE AGRIDEVEC/DI ( “UniqueNumber” FOR COLUMN UNIQUENO
INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
“YesNoFlag” FOR COLUMN YESNO CHAR(1) NOT NULL,
CONSTRAINT YESNO CHECK (“YesNoFlag” IN(‘Y’, ‘N’))
);
File 2
CREATE TABLE AGRIDEVEC/DIA
(
“YesNoFlag_1” FOR COLUMN YESNO_1,
“UniqueNumber_1” FOR COLUMN UNIQUENO_1
)
AS (SELECT
YESNO,
UNIQUENO
FROM DI
)
WITH NO DATA
Data in File
Yes Unique
No Number
Flag
d 0
d 0
If I use Like (File3) I get Position 4 Keyword LIKE not expected. Valid tokens: AS.
File3
CREATE TABLE AGRIDEVEC/DIB
(
“YesNoFlag_1” FOR COLUMN YESNO_1,
“UniqueNumber_1” FOR COLUMN UNIQUENO_1
)
LIKE (SELECT
YESNO,
UNIQUENO
FROM DI
)