DB2 Server for VSE & VM: Interactive SQL Guide and Reference

INPUT

>>-INPut----+-table_name-+---+---------------------------+----->< '-view_name--' | .-,-----------------. | | V | | '-(-----column_name--)---+--'

INPUT is an ISQL command that enables you to insert several rows of data into a table or view.

INPUT is issued to identify the table, view, or column(s) where the data is inserted.

table_name

view_name

is the name of the table or view in which data is to be inserted. A view_name can only be used if it applies to a single table. The names can contain DBCS characters.

column_name

is one or more column names, separated by commas, into which data is to be inserted. The order in which the column names are specified determines the order in which the data must be typed. All columns of the new row that are not listed receive a null value, and unlisted columns must have been defined to accept null values or an error occurs. This is because the INPUT command is essentially inserting a new row of data, and null values are inserted into any columns not specified. Omitting the column_name is the same as naming all the columns of the table in their created order. The names can contain DBCS characters.

Successful execution of the INPUT command causes the column names and their data types to be displayed in the order in which the data must be typed. Data can then be typed one row at a time. Press the ENTER key after each row is typed.

When typing data:

• Use commas to separate each data item of a row.
• Enclose the data item in single quotation marks if it is a CHAR, VARCHAR, DATE, TIME, or TIMESTAMP data type.
• Do not enclose the keyword NULL, or any of the special registers, such as CURRENT SERVER, in single quotation marks.
• If your data includes a single quotation mark, type two single quotation marks. When you type:

     'julie's book shop'
  

  JULIE'S BOOK SHOP is inserted into the table.

• If you are typing graphic data, you type:

     G'so...si'
  

  where so stands for shift-out character, si stands for shift-in character, and ... is graphic data (a DBCS character string).

  Note:

  You can use N' as a synonym for G'

• If you are typing hexadecimal data, you type X'F140F2'.
• If all the data for a single row does not fit in the input area, type the continuation character. Press ENTER to continue.
• Be sure to include a space before the continuation character, if required, because the continuation character does not provide one.
• When null values are allowed, you can type NULL to insert a null value for the data-item value.

After you press ENTER for a row of data, the data is moved to the output area of the display and the input area is cleared so that another row of data can be typed. When all the data has been typed, type the END command to signify the end of input data.

With AUTOCOMMIT on, the data you type is not committed to the table until the INPUT command is ended by the END command or the END PF key (usually PF3). You can use the ISQL SAVE command to commit data in the table prior to typing an END command.

The SAVE command stores all data typed since the previous SAVE command or, if one had not been typed, since the start of the INPUT command. It has the same effect as the END command but lets you continue to type data.

In addition to storing data prior to ending an INPUT command, you can also prevent the storing of data typed since the last SAVE command or since the start of the INPUT command if no SAVE has been issued. You type the ISQL BACKOUT command instead of more data.

If AUTOCOMMIT is off, the data you type on an INPUT command is not committed to the table until you type a COMMIT command after ending the INPUT command. With AUTOCOMMIT off, the SAVE and BACKOUT commands have no effect.

Example

The following illustrates how you can fill the ACTIVITY table by using the INPUT command:

   input activity
     .
     .
     .
   160,'ADMDB','Adm databases'
     .
     .
     .
   100,'TEACH','Teach classes'
   end