Open topic with navigation

TIBCO ActiveSpaces® - Enterprise Edition 5.0.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 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 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 that 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

Did you find this helpful?

Yes No
Great!
Thanks for taking the time to give us some feedback.
Sorry about that

Why wasn't this helpful? (check all that apply)

Could not find what I was looking for
Instructions confusing or unclear
Instructions did not work

Want to tell us more?

Please select at least one option or provide details about the issue!!

Error occurred while submitting feedback.

Error2 occurred while submitting feedback.

TIBCO ActiveSpaces® - Enterprise Edition

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