INPUT

The INPUT command inserts rows into an existing table.

Scope

ISQL only.

ISQL Syntax

INP[UT] {[Owner.] TableName [Owner.] ViewName } (ColumnName [,ColumnName] [ ... ]) { (Value [,Value] [ ... ]) [ ROLLBACK WORK COMMIT WORK ]} [ ... ] E[ND]

Parameters

[Owner.]TableName

identifies a table in the DBEnvironment you are using.

[Owner.]ViewName

identifies a view based on a single table. Refer to the discussion of the CREATE VIEW statement in the ALLBASE/SQL Reference Manual, for restrictions governing insert operations on a view.

The new rows are inserted into the table on which the view is based.

ColumnName

identifies the column(s) for which you will specify values. Columns not specified are assigned null values (if allowed).

You must provide data for any column that does not allow null values.

Value

is data to be inserted into a column. Within each set of parentheses, you specify one row of data; put a space, a carriage return, or a comma between sets of parentheses. A value must be specified for each ColumnName specified, in the order in which you named the columns. Provide data in the order in which the columns were named in the table or view definition. Character data that contains a single quote must double the single quote.

Values for a row can span lines, but do not split values across lines. Enclose character values in single quotation marks. To specify a null value, enter NULL.

ROLLBACK WORK

backs out rows entered since the last COMMIT WORK.

COMMIT WORK

performs a COMMIT WORK. Rows entered up to this point permanently update the DBEnvironment. A COMMIT WORK is also processed if the SET command's AUTOCOMMIT is on every time the number of rows specified in the AUTOSAVE option of the SET command has been entered.

END

terminates the INPUT command. If AUTOCOMMIT is on, ISQL issues a COMMIT WORK, and the following message appears:



   Number of rows processed is n

   DBEnvironment has been updated.

If AUTOCOMMIT is off, you must explicitly save any unsaved input, as indicated by the following message that appears after you enter END:

   Number of rows processed is n

   COMMIT WORK to save to DBEnvironment.

Description

You must establish a DBE session with the CONNECT or START DBE statement before using the INPUT command.
ISQL formulates an SQL INSERT statement from the information provided in the INPUT command.
The INPUT command is processed after you enter END, unless you are in prompting mode. In prompting mode, each row is inserted before you are prompted for the next row.

In prompting mode, ISQL prompts you for table names, column names, and data values as shown here:

   isql=> INPUT;



   Table name> TableName;

   Column names> (ColumnName1, ColumnName2  ...);

   1> (Value1, Value2,  'CharValue3');

   2> 

      .

.

.

   5> END;

The INPUT command assumes that you are inputting data in the default format for the data type. If you enter something other than a string, or if the string is the wrong size, an error results. An example of a correct INPUT command follows:

   isql=>INPUT  PurchDB.SupplyBatches(VendPartNumber,BatchStamp,

   MinPassRate);



   1> ('90015', '1984-07-09 11:09:18.432', 0.9732);

   2> ('7310',  '1984-07-14 10:11:13.824', 0.9794);

   3> END;

   Number of rows processed is 2

   COMMIT WORK to save to DBEnvironment.



   isql=>

Character data that contains a single quote must double the single quote. For example:

   isql=> INPUT PurchDB.Parts (PartNumber,PartName,SalesPrice)

   > ('2002-DA-01', 'Joe''s Plotter', 500.00)

   > END;

   Number of rows processed is 1

   COMMIT WORK to save to DBEnvironment.



   isql=>

Authorization

You must have authorization to insert a row into the named table or into the table on which the named view is derived. Refer to the discussion of the INSERT statement in the ALLBASE/SQL Reference Manual.

Example

   In prompting mode:



   isql=> INPUT PurchDB.Parts (PartNumber);



   1> ('2033-KLT-01');

   2> ('2034-BLT-01'),('999-99-9999');

   4> END;

   Number of rows processed is 3

   COMMIT WORK to save to DBEnvironment.



   isql=> COMMIT WORK;





   In nonprompting mode:



   isql=> INPUT PurchDB.SupplyPrice    (PartNumber,VendorNumber,VendPartNumber)

   > ('2033-KLT-01', 9002, 'A10')

   > ('2034-BLT-01', 9003, 'A12')

   > END;

   Number of rows processed is 2

   COMMIT WORK to save to DBEnvironment.



   isql=> COMMIT WORK;