INSERT statement

Creates a new database record, displays the initial values for the fields in the record, prompts for values of those fields, and assigns those values to the record.

The INSERT statement is a combination of the following statements:

• CREATE - Creates an empty record buffer
• DISPLAY - Moves the record from the record buffer into the screen buffer and displays the contents of the buffer on the screen
• PROMPT-FOR - Accepts input from the user, and puts that input into the screen buffer
• ASSIGN - Moves data from the screen buffer into the record buffer

Data movement

1. CREATE - Creates an empty record buffer
2. DISPLAY - Moves the contents of the record buffer to the screen buffer and displays the screen buffer
3. PROMPT-FOR - Accepts input from the user into the screen buffer
4. ASSIGN - Moves the contents of the screen buffer to the record buffer

Syntax

INSERT record [ EXCEPT field ... ]
  [ USING { ROWID ( nrow ) | RECID ( nrec ) } ]
  [ frame-phrase ]
  [ NO-ERROR ]

record

The name of the record you want to add to a database file. The AVM creates one record buffer for every file you use in a procedure. This buffer is used to hold a single record from the file associated with the buffer. Use the DEFINE BUFFER statement to create additional buffers, if necessary. The CREATE part of the INSERT statement creates an empty record buffer for the file in which you are inserting a record.

To insert a record in a table defined for multiple databases, you must qualify the record's table name with the database name. See the record definition in the Record phrase reference entry for more information.

EXCEPT field

Inserts all fields except those listed in the EXCEPT phrase.

USING { ROWID ( nrow ) | RECID ( nrec ) }

Allows you to insert a record in an RMS relative file (for backward compatibility only) using a specific record number, where nrow is the ROWID relative record number of the record you want to insert and nrec is the RECID relative record number of the record you want to insert.

frame-phrase

Specifies the overall layout and processing properties of a frame. For more information on frame-phrase, see the Frame phrase reference entry.

NO-ERROR

The NO-ERROR option is used to prevent the statement from raising ERROR and displaying error messages.

Example

In this procedure the user adds a new Order record. After the user adds a new Order record, the procedure creates OrderLines for that record. The procedure uses the CREATE statement to create OrderLines rather than the INSERT statement. When you use the INSERT statement, the PROMPT-FOR and ASSIGN parts of the INSERT let you put data into all the fields of the record being inserted. In the case of OrderLines, this procedure only lets you add information into a few of the OrderLine fields. Use CREATE together with UPDATE to single out the OrderLine fields.

r-insrt.p

REPEAT:
  INSERT Order WITH 1 COLUMN.
  REPEAT:
    CREATE OrderLine.
    OrderLine.OrderNum = Order.OrderNum.
    UPDATE OrderLine.LineNum OrderLine.ItemNum OrderLine.Qty 
      OrderLine.Price.
    /* Verify the ItemNum by finding an Item with that number */
    FIND Item OF OrderLine.
  END.
END.

Notes

• If an error occurs during the INSERT statement, the AVM retries the data entry part of the statement and processes the error associated with the block that contains the statement. (For example, an error might occur when the user enters a duplicate index value for a unique index.)
• When working with a partitioned table, creating a record without initially setting the partition fields to valid values may cause an error. See CREATE statement for more details.
• Any frame characteristics described by an INSERT statement contribute to the frame definition. When ABL compiles a procedure, it uses a top-to-bottom pass of the procedure to design all the frames required by that procedure, including those referenced by INSERT statements, and adds field and related format attributes as it goes through the procedure.
• If you receive input from a device other than the terminal, and the number of characters read by the INSERT statement for a particular field or variable exceeds the display format for that field or variable, the AVM returns an error. However, if you set a logical field that has a format of y/n and the data file contains a value of yes or no, the AVM converts that value to y or n.
• If you use a single qualified identifier with the INSERT statement, the compiler first interprets the reference as dbname.filename. If the compiler cannot resolve the reference as dbname.filename, it tries to resolve it as filename.fieldname.

  When inserting fields, you must use filenames that are different from field names to avoid ambiguous references. See the Record phrase reference entry for more information.

• The INSERT statement causes any related database CREATE triggers to execute. All CREATE triggers execute after the record is actually created. If a CREATE trigger fails (or executes a RETURN statement with the ERROR option), the record creation is undone.
• In the context of the .NET blocking method, System.Windows.Forms.Application:Run( ), if you directly or indirectly execute the INSERT statement while displaying a non-modal ABL window, in certain contexts (such as within a user-defined function or non-VOID method) this statement execution raises the STOP condition. For more information on the .NET Application:Run( ), method, see the reference entry for the WAIT-FOR statement (.NET and ABL).

See also

DEFINE BUFFER statement, Frame phrase, NO-ERROR option