INSERT

INSERT (Short Name: INS)

Use the INSERT command to add data to a table or view without using a data-entry form.

Options

(collist)
Specifies a list of one or more column names, separated by a comma (or the current delimiter). In an SQL command, any column name in the list can be preceded by a table or correlation name and a period (tblname.colname).

INTO tblview
Specifies the table or view name (views must be updatable).

SELECT clause
Finds values in a table, tables, or view to insert into the table or view specified by the INTO tblview option and the columns specified by the collist option.

VALUES (vallist)
Specifies a list of values to insert into the table specified by the INTO tblview option and the columns specified by the collist option. Separate values with a comma or the current delimiter.

For these data types...
Use this format for vallist

All data types except BIT, BITNOTE, LONG VARBIT, and VARBIT
'string' or value

BIT, BITNOTE, LONG VARBIT, LONG VARCHAR, VARBIT, and VARCHAR
['filename.ext'] or ['filename.ext', filetype, offset, length] Note: When you use VARCHAR, the filetype is always TXT. When you use VARBIT, BIT, and BITNOTE, filetype refers to the standard graphical file types.

About the INSERT Command

The INSERT command assigns a default value of null to any column not named in the collist unless a default value has been assigned to a column with the CREATE TABLE or ALTER TABLE command.

To ensure that rules are checked while adding data with the INSERT command, set RULES on before running the INSERT command.

The setting of the SET ZERO command affects the calculation of numeric computed columns. To have null values treated as zeros in expressions, set ZERO on. When ZERO is set off, if the value of a column used in a expression is null, the computed value will be null.

You cannot insert values into the table used in the SELECT clause.

To ensure that data is placed in the intended column, use the following guidelines:

Do not embed commas within entries for CURRENCY, DOUBLE, INTEGER, NUMERIC, or REAL data types. R:BASE automatically inserts commas and the current currency symbol.

When values for CURRENCY, DOUBLE, NUMERIC, or REAL or data types are decimal fractions, you must enter the decimal point. When values are whole numbers, R:BASE adds a decimal point for you at the end of the number. R:BASE adds zeros for subunits in whole currency values; For example, using the default currency format, R:BASE loads an entry of 1000 as $1,000.00.

When values for NOTE or TEXT data types contain commas, you can either enclose the entries within quotes, or use SET DELIMIT to change the default delimiter (comma) to another character.

When values for NOTE or TEXT data types contain single quotes ('), and you are using the default QUOTES character ('), use two single quotes ('') in the text string. For example, 'Walter Finnegan''s order.'

When a value you specify for a column is not the same data type as the column's data type, R:BASE displays an error message and you need to re-enter the entire row.

When values for NOTE or TEXT data types exceed the maximum length of a column, R:BASE truncates the value and adds it to the table. A message is displayed that tells you which row has been truncated.

Inserting an Autonumbered Column

When you use INSERT to add a row, INSERT assigns the next available number to autonumbered columns in the table. Therefore, omit autonumbered columns and their values from a collist.Also, if you use the SELECT option, omit an autonumbered column from the collist. If a value is included for an autonumbered column that was omitted from the column list, R:BASE does not run the command because it cannot identify which column to load.

Inserting a Computed Column

Because a computed column's value is calculated, you cannot insert a new value. Omit computed columns from a collistor, if you are adding data to all columns, do not use a collist and do not specify a value for the computed column. R:BASE will skip the computed column when the row is inserted.

Examples

In the following example, the sales table has three columns, col1, col2, and col3, and col2 is a computed column. To insert a row, you would only specify values for col1 and col3. In this example, the value for col1 is 100, and the value for col3is 200.

If the expression for col2 was (col1 + 200), then col2 would have the value 300 when the row is inserted.

INSERT INTO sales VALUES (100, 200)

In the following example, a vallist adds a new row to the product table, filling the model, prodname, proddesc, and listprice columns.

INSERT INTO product (model, prodname, proddesc, listprice) +
   VALUES ('PB3060', 'Portable Advanced PC', 'System-Single +
   Drive w/Hard Disk-Portable', 3795)

The following command uses a vallist with global variables to insert the values from variables v1, v2, and v3 into the bonusrate table.

SET VARIABLE v1 CURRENCY = 50000, v2 CURRENCY = 75000, +
   v3 REAL = .10
INSERT INTO bonusrate VALUES (.v1, .v2, .v3)

The following example adds rows to customer table selected from temp table. It adds data into the company and custphone columns. The columns taken from the temp table can have different column names, the data types must be the same, and the order and number of columns in the column list of the source table (designated by SELECT) must match the column list of the destination table (designated by INTO).

INSERT INTO customer (company, custphone) +
   SELECT cname, phone FROM temp

For these data types...	Use this format for *vallist*
All data types except BIT, BITNOTE, LONG VARBIT, and VARBIT	'string' or value
BIT, BITNOTE, LONG VARBIT, LONG VARCHAR, VARBIT, and VARCHAR	['filename.ext'] or ['filename.ext', filetype, offset, length] Note: When you use VARCHAR, the filetype is always TXT. When you use VARBIT, BIT, and BITNOTE, filetype refers to the standard graphical file types.