SQL Reference Guide

INSERT

Description

Inserts new rows into the specified table/view that will contain either the explicitly specified values or the values returned by the query expression.

Syntax

//Inserting one record

INSERT INTO [owner_name.] { table_name | view_name }

[ (column_name, column_name, ... ) ]

{ VALUES (value, value, ... )

//Inserting multiple records

INSERT INTO [owner_name.] { table_name | view_name }

[ (column_name, column_name, ... ) ]

{ VALUES (value1-1, value1-2, ... value1-n),

(value2-1, value2-3, ... value2-n),

...

};

//Inserting records from a query

INSERT INTO [owner_name.] { table_name | view_name }

[ (column_name, column_name, ... ) ]

query_expression ;

Notes

• If the optional list of column names is specified, then only the values for those columns need be supplied. The rest of the columns of the inserted row will contain NULL values, provided the table definition allows NULL values and there is no DEFAULT clause for the columns. If a DEFAULT clause is specified for a column and the column name is not present in the optional column list, then the column takes the default value.
• If the optional list is not specified then all the column values have to be either explicitly specified or returned by the query expression. The order of the values should be the same as the order in which the columns have been declared in the declaration of the table/view.
• Explicit specification of the column values provides for insertion of only one row at a time. The query expression option allows for insertion of multiple rows at a time.
• If the table contains a foreign key, and there does not exist a corresponding primary key that matches the values of the foreign key in the record being inserted, the insert operation is rejected.
• You can use INSERT statements with query expressions to transfer rows from one remote table to another.
• IDENTITY columns are populated automatically based on the seed and increment values that were specified for the IDENTITY column, values for the IDENTITY column cannot be specified as part of the INSERT statement. An IDENTITY column cannot be specified in the list of column names.
• Multiple value sets are allowed.
  
  ISQL>create table mult (f1 int, f2 int);
  ISQL>insert into mult values (1,1),(2,2),(3,3);

  Parameters are not allowed in multiple value sets.

Examples

//Create table alerts

create table alerts

(

id integer not null identity (1,1)

, date_time timestamp

, alert_type character(20)

, description varchar(200)

, constraint alerts_constraint_pk primary key ( id )

);

//Inserting one record

INSERT INTO alerts

( date_time, alert_type, description )

VALUES

( '01/03/2022 15:16:10.000', 'NOTICE', 'Information' );

//Inserting records from a recordset

INSERT INTO alerts ( date_time, alert_type, description )

VALUES

( '02/04/2023 16:27:21.000', 'WARNING ', 'Minor alert' )

, ( '03/05/2024 17:38:32.000', 'CRITICAL', 'Major alert' )

, ( '04/06/2025 18:49:43.000', 'FAILURE ', 'P1' );

//Inserting records from a SELECT

INSERT INTO alerts ( date_time, alert_type, description )

SELECT date_time, alert_type, description FROM alerts ;

Authorization

The user executing this statement must have any of the following privileges:

• DBA privilege.
• Ownership of the table.
• INSERT privilege on the table.

If a query_expression is specified, then the user must have any of the following privileges:

• DBA privilege.
• SELECT privilege on all the tables/views referred to in the query_expression.

  SQL Compliance	SQL-92, ODBC Core SQL grammar
  Environment	Embedded SQL, interactive SQL, ODBC applications
  Related Statements	Query Expressions