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 need 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.
TIBCO ActiveSpaces® Concepts
— 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.
Adding Rows To a Table by Using the SQL INSERT Statement Procedure
1. Formulate a SQL INSERT string for adding rows of data to a table.
2. Call the createStatement() method of the Session object. Pass the SQL INSERT string as an argument.
3. If the SQL INSERT string contains parameter markers, call the Statement methods to set the parameter values.
4. Run the INSERT statement by calling the executeUpdate() method of the Statement object.
5. Check the result of the executeUpdate() method to verify that the correct number of rows have been inserted into the table.
6. Optional: Set different parameter values and rerun the INSERT statement by repeating steps Step 3 to Step 5.
7. Close the Statement object.
Errors
By default, when an error occurs for an INSERT statement, changes made by the statement are undone.
If the INSERT statement is called from within an existing transaction, the entire transaction is rolled back. ActiveSpaces does not impose a limit to the number of rows in a multi-row insertion. However, the number of rows must be processed within the time specified by the client request timeout
property of the data grid (The default is 5 seconds).
If the insert values list does not match column list, a SQL command error is returned. Errors due to a conflict with the following constraints:
Constraints Error Description
PRIMARY KEY Values are not provided for all primary key
columns
INSERT Statement and Expressions
INSERT statements can contain expressions for the values to be inserted for a column. For example, the following INSERT statement can be used to add a timestamp as the primary key for each row being added:
INSERT INTO mytable (col1, col2) VALUES (datetime(‘now’), ‘some string’)
The expressions that can be used as values are limited and cannot contain column names that must be dynamically evaluated.
Parameter Binding with INSERT Statements
When you use parameter binding, you use "?" (question marks) instead of actual values in a SQL statement. The "?" parameter must be used in place of a value for that column and not in arbitrary expressions.
The current parameter bindings are used whenever executeUpdate() is invoked for an INSERT statement. Parameter bindings can also be used with multi-row inserts.
Example of parameter binding:
INSERT INTO mytable (col1, col2) VALUES (?,?),(?,?),(?,?)
The maximum number of parameters on a given INSERT statement is 999.