Thursday, October 2, 2014

Defining SQL tables using a reference file, continued

sql create table like

Yesterday I showed how to create SQL tables using a reference file. Tommaso Arcieri message me with another approach, defining a table based on another file or table.

In this example I am going to use the physical file TESTFILE as the original reference file.

  A          R TESTFILER
  A            FLD001        10
  A            FLD002         3P 0
  A            FLD003        10
  A            FLD004         5P 0
  A            FLD005         9P 2
  A            FLD006         7P 2
  A            FLD007         1
  A            FLD008         1
  A          K FLD001

This just a small file of only eight fields, so it would not be a big deal to enter each field name. But for a field with many more fields it would be a pain to do so. Fortunately the approach Tommaso showed me should be used if the original reference file has only one field or hundreds.

I will be using the CREATE TABLE as I did yesterday, but I will not be using a SELECT to list the columns I want in the table. As I want all the fields in TESTFILE to be columns in my new table I use a LIKE clause:


As expected the LIKE clause specifies that the columns in the new table will be exactly the same as the fields or columns in the original reference file. If I run the 'Display File Field Description' command, DSPFFD, you can see that the columns are the same as the original file:

             Data        Field
  Field      Type       Length
  FLD001     CHAR           10
  FLD002     PACKED       3  0
  FLD003     CHAR           10
  FLD004     PACKED       5  0
  FLD005     PACKED       9  2
  FLD006     PACKED       7  2
  FLD007     CHAR            1
  FLD008     CHAR            1

If your new table is not in the library QTEMP you can add a key to it using ALTER TABLE:


If the new table is in QTEMP you need to read Add key to SQL table in QTEMP.

You can see how to add the equivalent of Column Headings and Text using LABEL ON COLUMN in yesterday's post.


More information about CREATE TABLE can be found on the IBM website here.


This article was written for IBM i 7.1, and it should work with earlier releases too.


  1. Hi Simon,

    Can you please let me know how can I create a table using keys of other Table.


    1. I am not sure what you mean by your question.

      Are you wanting to make a file that is just made up for columns that a foreign keys?

      Or are you asking how to copy the keys from to a table from the reference file/table?


To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.