Open topic with navigation

TIBCO ActiveSpaces® 4.8.0

# The SQL INSERT Statement

A SQL INSERT statement writes new rows of data into a table. If the INSERT activity is successful, it returns the number of rows inserted into the table. If the row already exists, it returns an error. Multiple rows can be inserted into a table. Multi-row inserts treat the insertion of rows equivalent to being in a transaction whether or not a transacted session is used. If the INSERT statement is embedded in a transaction or if you are trying to insert multiple rows, the failure to insert a row results in a rolled back transaction.

## Syntax

    INSERT INTO <table_name> [(<column_name_list>)] VALUES (<column_value_list>)[,(<column_value_list>)]...

where

- `table_name`: Name of the table in which the rows are inserted.
- `column_name_list`: Each column name in `column_name_list` that identifies a column of `table_name`.
- `VALUES`: Values for the columns in `column_name_list`.
- `column_value_list`: A comma separated list of values.

The following table shows the syntax for inserting rows in a table:

Number of Rows | Syntax
--- | ---
Single Row Insert |
    INSERT INTO <table_name> [(column1 [,
    column2, column3 …])] VALUES (value1 [, value2, value3 …])
Multi-Row Insert |
    INSERT INTO <table_name>  [(column-a, [column-b, …])]
    VALUES (‘value-1a’, [‘value-1b’, …]),
    (‘value-2a’, [‘value-2b’, …]),
    . . .

## Rules for the column_name_list

- Each of the named columns of the new row is populated with the results only after evaluating the corresponding `VALUES` expression.
- If `column_name_list` is omitted, the number of values inserted into each row must be the same as the number of columns in the table.
  - Values are populated into the row in the order the columns were defined for in the table.
- All columns in `column_name_list` must be writable.
- No column name can be listed more than once.
- Not all columns of a table have to be listed. The value of any unlisted column is `NULL` (empty).
  - If a value is not provided for a primary key column, an error is reported.

## Rules for the column_value_list

- The data type of each value must match the data type of the column as configured for the table.
- If `column_name_list` is given, the number of values must match the number of columns in `column_name_list`.
- If `column_name_list` is given, the nth column in `column_name_list` is assigned the nth value of `column_value_list`.
- If `column_name_list` is omitted, the number of values must match the number of columns in the table with an implied ascending sequence of the ordinal positions of columns in the table.
- A value can be the result of an expression.
- A value can be a parameter marker that requires a value to be bound to the parameter before the statement is run.
  - The maximum length of a SQL statement is `1,000,000` bytes.
  - Parameters must be used to insert data that can cause the statement to reach the maximum length.
  - The parameter type must match the column type exactly. Call the appropriate setParameter() method - the type of the parameter passed to this method must match with the column type.

For more information about using the SQL INSERT statement to store values in a `tibDateTime` column, see tibDateTime.

Subtopics

Copyright © 2009-2022. Cloud Software Group, Inc. All Rights Reserved.