INSERT Statement

Quick Links:

Syntax

Syntax 1

INSERT [ INTO ] [ <owner>.]<table-name> [ ( <column-name> [, …] ) ]
    ... VALUES ( [ <expression> | DEFAULT,… ) ]
or
   INSERT [ INTO ] [ <owner>.]<table-name> DEFAULT VALUES

Syntax 2

INSERT [ INTO ] [ <owner>.]<table-name> [ ( <column-name> [, …] ) ]
   ... insert-load-options insert-select-load-options
   ... <select-statement>

Syntax 3

INSERT [ INTO ] [ <owner>.]<table-name>[ ( <column-name> [, …] ) ]
    ... insert-select-load-options insert-select-load-options
  LOCATION '<servername.dbname>'
   [ location-options ]
   ... { { <select-statement> } | ‘<select statement>’ }

insert-load-options - (back to Syntax 2) or (back to Syntax 3)
   [ LIMIT <number-of-rows> ] 
   [ NOTIFY <number-of-rows> ] 
   [ SKIP <number-of-rows> ] 

insert-select-load-options - (back to Syntax 2) or (back to Syntax 3)
   [ WORD SKIP <number> ]
   [ IGNORE CONSTRAINT constraint-type [, …] ] 
   [ MESSAGE LOG ‘<string>’ ROW LOG ‘<string>’ [ ONLY LOG logwhat [, …] ] ] 
   [ LOG DELIMITED BY ‘<string>’ ]

constraint-type - (back to insert-select-load-options)
   { CHECK <integer> 
   | UNIQUE <integer> 
   | NULL <integer> 
   | FOREIGN KEY <integer> 
   | DATA VALUE <integer> 
   } ALL <integer> 
   }

logwhat - (back to insert-select-load-options)
   { CHECK 
   | ALL 
   | NULL
   | UNIQUE
   | DATA VALUE
   | FOREIGN KEY
   | WORD 
   }

Parameters

(back to top)

insert-load-options
options that constrain the load:
- LIMIT specifies the maximum number of rows to insert into the table from a query. The default is 0 for no limit. The maximum is 2GB -1.
- NOTIFY specifies that you be notified with a message each time the number of rows are successfully inserted into the table. The default is every 100,000 rows.
- SKIP defines the number of rows to skip at the beginning of the input tables for this insert. The default is 0.

WORD SKIP allows the load to continue when it encounters data longer than the limit specified when the word index was created. The <number> parameter specifies the number of times to ignore the error. Setting this option to 0 means there is no limit.
If a row is not loaded because a word exceeds the maximum permitted size, a warning is written to the .iqmsg file. WORD size violations can be optionally logged to the MESSAGE LOG file. If the option is not specified, the operation rolls back on the first occurrence of a word that is longer than the specified limit.

IGNORE CONSTRAINT determines whether the load engine ignores CHECK, UNIQUE, NULL, DATA VALUE, and FOREIGN KEY integrity constraint violations that occur during a load and the maximum number of violations to ignore before initiating a rollback.
If <limit> is zero, the number of CHECK constraint violations to ignore is infinite. If CHECK is not specified, the first occurrence of any CHECK constraint violation causes the load to roll back. If <limit> is nonzero, then the <limit> +1 occurrence of a CHECK constraint violation causes the load to roll back

MESSAGE LOG specifies the file names where the load engine logs integrity constraint violations. Timestamps indicating the start and completion of the load are logged in both the MESSAGE LOG and the ROW LOG files. Both MESSAGE LOG and ROW LOG must be specified, or no information about integrity violations is logged.
Information is logged on all integrity constraint-type violations specified in the ONLY LOG clause or for all word index-length violations if the keyword WORD is specified. If theONLY LOG clause is not specified, no information on integrity constraint violations is logged. Only the timestamps indicating the start and completion of the load are logged.

LOG DELIMITED BY specifies the separator between data values in the ROW LOG file. The default separator is a comma.

ENCRYPTED PASSWORD specifies the use of Open Client Library default password encryption when connecting to a remote server. If you specify this parameter and the remote server does not support Open Client Library default password encryption, an error is reported indicating that an invalid user ID or password was used.
To enable the SAP IQ server to accept a jConnect connection with an encrypted password, set the jConnect ENCRYPT_PASSWORD connection property to true.

PACKETSIZE specifies the TDS packet-size in bytes. The default TDS packet-size on most platforms is 512 bytes. If the packet size is not specified or is specified as zero, then the default packet size value for the platform is used.
The packet-size value must be a multiple of 512, either equal to the default network packet size or between the default network packet size and the maximum network packet size. The maximum network packet size and the default network packet size are multiples of 512 in the range 512 – 524288 bytes. The maximum network packet size is always greater than or equal to the default network packet size.
QUOTED_IDENTIFIER
sets the QUOTED_IDENTIFIER option on the remote server. The default setting is 'OFF.' You set QUOTED_IDENTIFIER to ‘ON’ only if any of the identifiers in the SELECT statement are enclosed in double quotes, as in this example using ‘c1’:
```
INSERT INTO foo
LOCATION 'ase.database'
QUOTED_IDENTIFIER ON {select "c1" from xxx}; 
```

ISOLATION LEVEL specifies an isolation level for the connection to a remote server:

Isolation Level	Characteristics
READ UNCOMMITTED	Isolation level 0 Read permitted on row with or without write lock No read locks are applied No guarantee that concurrent transaction will not modify row or roll back changes to row
READ COMMITTED	Isolation level 1 Read only permitted on row with no write lock Read lock acquired and held for read on current row only, but released when cursor moves off the row No guarantee that data will not change during transaction
SERIALIZABLE	Isolation level 3 Read only permitted on rows in result without write lock Read locks acquired when cursor is opened and held until transaction ends

Note

For additional information on the insert-select-load-options and location-options as well as the constraint-type and logwhat parameters, see the LOAD TABLE Statement.

Examples

(back to top)

Example 1 add an Eastern Sales department to the database:

INSERT INTO Departments
(DepartmentID, DepartmentName, DepartmentHeadID)
VALUES (600, 'Eastern Sales', 501)

Example 2 fill the table dept_head with the names of department heads and their departments:

INSERT INTO dept_head (name, dept)
  NOTIFY 20
  SELECT Surname || ' ' || GivenName
  AS name,
  dept_name
FROM Employees JOIN Departments
  ON EmployeeID= DepartmentHeadID

Example 3
insert data from the l_shipdate and l_orderkey columns of the lineitem table from the SAP IQ database iqdet on the remote server detroit into the corresponding columns of the lineitem table in the current database:
```
INSERT INTO lineitem
  (l_shipdate, l_orderkey)
  LOCATION 'detroit.iqdet'
  PACKETSIZE 512
  ' SELECT l_shipdate, l_orderkey
FROM lineitem '
```
Example 4
the INSERT statement permits a list of values allowing several rows to be inserted at once.
```
INSERT into t1 values( 10, 20, 30 ), ( 11, 21, 31 ), ( 12, 22, 32 )
```

Usage

(back to top)

Syntax 1 allows the insertion of a single row with the specified expression values. If the list of column names is not specified, the values are inserted into the table columns in the order they were created (the same order as retrieved with SELECT *). The row is inserted into the table at an arbitrary position. (In relational databases, tables are not ordered.)

Syntax 2 allows the user to perform a mass insertion into a table using the results of a fully general SELECT statement. Insertions are done in an arbitrary order unless the SELECT statement contains an ORDER BY clause. The columns from the select list are matched ordinally with the columns specified in the column list, or sequentially in the order in which the columns were created.

Note

The NUMBER(*) function is useful for generating primary keys with Syntax 2 of the INSERT statement.

Syntax 3 INSERT...LOCATION is a variation of Syntax 2 that allows you to insert data from an SAP ASE or SAP IQ database. The <servername.dbname> specified in the LOCATION clause identifies the remote server and database for the table in the FROM clause. To use Syntax 3, the SAP ASE or SAP IQ remote server to which you are connecting must exist in the SAP Open Client interfaces or sql.ini file on the local machine.

In queries using Syntax 3, you can insert a maximum of 2147483647 rows.

The SELECT statement can be delimited by either curly braces or straight single quotation marks.

Note Curly braces represent the start and end of an escape sequence in the ODBC standard, and might generate errors in the context of ODBC or SAP IQ Cockpit. The workaround is to use single quotes to escape the SELECT statement.

The local SAP IQ server connects to the server and database you specify in the LOCATION clause. The results from the queries on the remote tables are returned and the local server inserts the results in the current database. If you do not specify a server name in the LOCATION clause, SAP IQ ignores any database name you specify, since the only choice is the current database on the local server.

When SAP IQ connects to the remote server, INSERT...LOCATION uses the remote login for the user ID of the current connection, if a remote login has been created with CREATE EXTERNLOGIN and the remote server has been defined with a CREATE SERVER statement. If the remote server is not defined, or if a remote login has not been created for the user ID of the current connection, SAP IQ connects using the user ID and password of the current connection.

Note If you rely on the user ID and password of the current connection, and a user changes the password, you must stop and restart the server before the new password takes effect on the remote server. Remote logins created with CREATE EXTERNLOGIN are unaffected by changes to the password for the default user ID.

Creating a remote login with the CREATE EXTERNLOGIN statement and defining a remote server with a CREATE SERVER statement sets up an external login and password for INSERT...LOCATION such that any user can use the login and password in any context. This avoids possible errors due to inaccessibility of the login or password, and is the recommended way to connect to a remote server.

For example, user russid connects to the SAP IQ database and executes this statement:

INSERT local_SQL_Types LOCATION ‘ase1.ase1db’
{SELECT int_col FROM SQL_Types};

On server ase1, there exists user ID ase1user with password sybase. The owner of the table SQL_Types is ase1user. The remote server is defined on the IQ server as:

CREATE SERVER ase1 CLASS ‘ASEJDBC’
USING ‘system1:4100’;

The external login is defined on the IQ server as:

CREATE EXTERNLOGIN russid TO ase1 REMOTE LOGIN ase1user IDENTIFIED BY sybase;

INSERT...LOCATION connects to the remote server ase1 using the user ID ase1user and the password sybase for user russid.

Use the ENCRYPTED PASSWORD parameter to specify the use of Open Client Library default password encryption when connecting to a remote server. If ENCRYPTED PASSWORD is specified and the remote server does not support Open Client Library default password encryption, an error is reported indicating that an invalid user ID or password was used.

When used as a remote server, SAP IQ supports TDS password encryption. The SAP IQ server accepts a connection with an encrypted password sent by the client. For information on connection properties to set for password encryption, see Software Developer's Kit 15.5 > Open Client Client-Library/C Reference Manual > Client-Library Topics > Security features > SAP ASE security features > Security handshaking: encrypted password Information published on SAP site for Open Server 15.5.

Note

Password encryption requires Open Client 15.0. TDS password encryption requires Open Client 15.0 ESD #7 or later.

When INSERT...LOCATION is transferring data between an SAP IQ server and a remote SAP IQ or SAP ASE server, the value of the INSERT...LOCATION TDS PACKETSIZE parameter is always 512 bytes, even if you specify a different value for PACKETSIZE.

Note

If you specify an incorrect packet size (for example 933, which is not a multiple of 512), the connection attempt fails with an Open Client ct_connect “Connection failed” error. Any unsuccessful connection attempt returns a generic “Connection failed” message. The SAP ASE error log might contain more specific information about the cause of the connection failure.

SAP IQ does not support the SAP ASE data type TEXT, but you can execute INSERT...LOCATION (Syntax 3) from both an IQ CHAR or VARCHAR column whose length is greater than 255 bytes, and from an ASE database column of data type TEXT. ASE TEXT and IMAGE columns can be inserted into columns of other SAP IQ data types, if SAP IQ supports the internal conversion. By default, if a remote data column contains over 2GB, SAP IQ silently truncates the column value to 2GB.

Caution

SAP IQ does not support the SAP ASE data types UNICHAR, UNIVARCHAR, or UNITEXT. An INSERT...LOCATION command from UNICHAR or UNITEXT to CHAR or CLOB columns in the ISO_BINENG collation may execute without error; if this happens, the data in the columns may be inconsistent. An error is reported in this situation, only if the conversion fails.

Users must be specifically licensed to use the large object functionality of the Unstructured Data Analytics Option.

Note

If you use INSERT...LOCATION to insert data selected from a VARBINARY column, set ASE_BINARY_DISPLAY to OFF on the remote database.

INSERT...LOCATION (Syntax 3) does not support the use of variables in the SELECT statement.

Inserts can be done into views, provided the SELECT statement defining the view has only one table in the FROM clause and does not contain a GROUP BY clause, an aggregate function, or involve a UNION operation.

Character strings inserted into tables are always stored in the case they are entered, regardless of whether the database is case-sensitive or not. Thus, a string “Value” inserted into a table is always held in the database with an uppercase V and the remainder of the letters lowercase. SELECT statements return the string as 'Value.' If the database is not case-sensitive, however, all comparisons make 'Value' the same as 'value,' 'VALUE," and so on. Further, if a single-column primary key already contains an entry Value, an INSERT of value is rejected, as it would make the primary key not unique.

Whenever you execute an INSERT...LOCATION statement, SAP IQ loads the localization information needed to determine language, collation sequence, character set, and date/time format. If your database uses a nondefault locale for your platform, you must set an environment variable on your local client to ensure that SAP IQ loads the correct information.

If you set the LC_ALL environment variable, SAP IQ uses its value as the locale name. If LC_ALL is not set, SAP IQ uses the value of the LANG environment variable. If neither variable is set, SAP IQ uses the default entry in the locales file.

Use the (DEFAULT), DEFAULT VALUES or VALUES() clauses to insert rows with all default values. Assuming that there are 3 columns in table t2, these examples are semantically equivalent:

INSERT INTO t2 values (DEFAULT, DEFAULT, DEFAULT);

INSERT INTO t2 DEFAULT VALUES;

INSERT INTO t2() VALUES();

INSERT...VALUES also supports multiple rows. The following example inserts 3 rows into table t1:

CREATE TABLE t1(c1 varchar(30));
INSERT INTO t1 VALUES  ('morning'),('afternoon'),
    ('evening');

SAP IQ treats all load/inserts as full-width inserts. Columns not explicitly specified on the load/insert statement, the value loaded will either be the column’s DEFAULT value (if one is defined) or NULL (if no DEFAULT value is defined for the column).

SAP IQ supports column DEFAULT values for INSERT...VALUES, INSERT...SELECT, and INSERT...LOCATION. If a DEFAULT value is specified for a column, this DEFAULT value is used as the value of the column in any INSERT (or LOAD) statement that does not specify a value for the column.

An INSERT from a stored procedure or function is not permitted, if the procedure or function uses COMMIT, ROLLBACK, or some ROLLBACK TO SAVEPOINT statements.

The result of a SELECT…FROM may be slightly different from the result of an INSERT…SELECT…FROM due to an internal data conversion of an imprecise data type, such as DOUBLE or NUMERIC, for optimization during the insert. If a more precise result is required, a possible workaround is to declare the column as a DOUBLE or NUMERIC data type with a higher precision.

Standards

(back to top)

SQL—ISO/ANSI SQL compliant.
SAP Database products—Supported by SAP ASE (excluding the <insert-load-options>).

Permissions

(back to top)

Requires INSERT privilege on the table.