Duplicating CPYF Function Using SQL

March 16, 2011 Skip Marchesani

There are a few questions that I am frequently asked re: SQL that are interrelated. First, can SQL be used to duplicate the function found in the native CPFY (copy file) command? Next, can SQL be used to duplicate a table similar to the function found in the native CRTDUPOBJ (create duplicate object) command? And lastly, can a field reference file be used as the source for column definitions with the CREATE TABLE SQL statement? The answer is a qualified YES to all three questions, and it is relatively easy to do.

From a SQL perspective, I break the CPYF function into two areas. One is the ability to copy the data from one or more columns that exist in one or more source tables and insert the resulting row(s) into an existing target table. The other is the ability to create a target table on the fly and then copy the data from one or more columns that exist in one or more source tables and insert the resulting row(s) into the newly created target table.

At a high level, the INSERT INTO SQL statement with a subselect allows the selection and copying of columns from one or more source tables and the insertion of the resulting row into an existing target table. And, the CREATE TABLE SQL 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 rows into the newly created target table.

The CREATE TABLE SQL statement with a subselect also can 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.

Note that a subselect is a SELECT statement embedded 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.

This article will review and discuss using the INSERT INTO SQL statement in conjunction with a subselect, and my next article will review and discuss using the CREATE TABLE SQL statement in conjunction with a subselect.

To show how the INSERT INTO statement with a subselect can be used to copy data, I will review and discuss two examples. The first example will select and copy data from a single source table and insert the resulting rows into an existing target table. The second example will select and copy data from two source tables and insert the resulting rows into an existing target table.

For the first example the source table is an employee table called EMP, which has the format and rows shown below:

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

The target table for the first example is a work table called EMPNAME1, initially contains no rows, and has the following columns and format.

Number	Employee Number (same as NBR in EMP)
Name		Employee Name (same as Name in EMP)
Dept		Department Number (same as DPT in EMP and DEP)

The EMPAME1 work table can be populated with all the rows from the EMP table by using the following INSERT INTO SQL statement with subselect.

	INSERT INTO   empname1   (number,   name,   dept)
      		SELECT   nbr,   nam,   dpt   FROM   emp

The SQL statement SELECT nbr, nam, dpt FROM emp is the subselect.

After executing the INSERT INTO statement, the EMPNAME1 table contains the following rows.

EMPNAME1 Table
Number		Name           Dept
20	       Heikki    	      901
10	       Ed        	      911
50	       Marcela   	      911
40	       Mike      	      977
30	       John      	      977
60	       Frank     	      990

The SELECT statement for the subselect can be as simple or complex as required. It can contain any valid SELECT statement clause including the WHERE clause for row selection, the GROUP BY and HAVING clauses for row summarization, and the ORDER BY clause if the order of rows written to the work table is important.

If we compare this INSERT INTO example to CPYF, it is the equivalent of copying all rows from a single source table 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.

Next let’s look at an example that copies data from multiple source tables into a single target table. This second example will again use the employee table called EMP as one source table and add a department table called DEP as a second source table. The format and rows in the EMP table remains the same and the DEP table has the following format and rows.

DEP Table
Dpt      Dnm
901	Accounts
977	Manufact
911	Sales
990	Spares

The target table for the second example is a work table called EMPNAME2, initially contains no rows, and has the following columns and format.

Number	Employee Number (same as NBR in EMP)
Name		Employee Name (same as Name in EMP)
Dept		Department Number (same as DPT in EMP and DEP)
Dptnam	Department Name (same as DNM in DEP)

Note that the EMP table does not have a column with the department name, but the DEP table does. This means that the subselect for the INSERT INTO SQL statement has to select and copy columns from two source files–EMP and DEP–and insert the resulting rows into EMPNAME2, the existing target table. This is accomplished by adding a second or inner subselect to the column list of the first or outer subselect, immediately after the column called DPT.

In this example EMPNAME2 will be populated only with the specified columns from rows where the dept (department number) is equal to 911 or 977. To perform this selection criteria a WHERE clause (WHERE dpt IN(911, 977)) has been added to the SELECT statement for the outer subselect of the INSERT INTO statement.

The outer subselect along with its inner subselect and WHERE clause has the following syntax.

		SELECT   nbr,   nam,   dpt,   
                                         (SELECT   dnm   
                                                    FROM   dep   b
                                                    WHERE   a.dpt   =   b.dpt)   
                                                AS   dptnam    
                                 FROM   emp   a
                                 WHERE   dpt  IN(911, 977)

The entire INSERT INTO statement and its associated outer and inner subselects is shown below.

	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)

After executing the INSERT INTO SQL statement, the EMPNAME2 table 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 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 that 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.

My next article will continue the discussion and explore duplicating an entire table or a subset of a table using the CREATE TABLE SQL statement.

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.