The SQL INSERT Statement

An 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.
    • The maximum length of an 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.
Related concepts