INSERT Statement

Inserts into a table either a single row (Syntax 1) or a selection of rows (Syntax 2) from elsewhere in the current database. Inserts a selection of rows from another database (Syntax 3).

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-load-options insert-select-load-options
LOCATION 'servername.dbname'
[ location-options ]
... { { select-statement } | ‘select statement’ }

Parameters

insert-load-options: – [ LIMIT number-of-rows ] [ NOTIFY number-of-rows ] [ SKIP number-of-rows ] [ START ROW ID number ]
insert-select-load-options: – [ WORD SKIP number ] [ IGNORE CONSTRAINT constrainttype [, …] ] [ MESSAGE LOG ‘string’ ROW LOG ‘string’ [ ONLY LOG logwhat [, …] ] ] [ LOG DELIMITED BY ‘string’ ]
constrainttype: – { CHECK integer | UNIQUE integer | NULL integer | FOREIGN KEY integer | DATA VALUE integer | ALL integer }
logwhat: – { CHECK | ALL | NULL | UNIQUE | DATA VALUE | FOREIGN KEY | WORD }
location-options: – [ ENCRYPTED PASSWORD ] [ PACKETSIZE packet-size ] [ QUOTED_IDENTIFIER { ON | OFF } ] [ ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | SERIALIZABLE } ]

Examples

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 Sybase 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 (Catalog Store Only) – This example inserts three rows into a table. Inserts containing more than one list of values are supported on Catalog Store (SQL Anywhere) tables only. This syntax results in an error if used on an IQ table.
```
INSERT INTO T (c1,c2,c3)
VALUES (1,10,100), (2,20,200), (3,30,300);
```
Example 5 (Catalog Store Only) – The INSERT statement in this example inserts three rows into a table of four columns where each column has a default value. Inserts containing more than one list of values are supported on Catalog Store (SQL Anywhere) tables only. This syntax results in an error if used on an IQ table.
```
INSERT INTO T ()
VALUES (), (), ();
```

Usage

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. See Reference: Building Blocks, Tables, and Procedures > SQL Functions.

Syntax 3 INSERT...LOCATION is a variation of Syntax 2 that allows you to insert data from an Adaptive Server Enterprise or Sybase 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 Adaptive Server Enterprise or Sybase IQ remote server to which you are connecting must exist in the Sybase 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 Sybase Central. The workaround is to use single quotes to escape the SELECT statement.

The local Sybase 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, Sybase IQ ignores any database name you specify, since the only choice is the current database on the local server.

When Sybase 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, Sybase 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 Sybase 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, Sybase IQ supports TDS password encryption. The Sybase 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 > Adaptive Server Enterprise security features > Security handshaking: encrypted password 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.

To enable the Sybase IQ server to accept a jConnect connection with an encrypted password, set the jConnect ENCRYPT_PASSWORD connection property to true.

The PACKETSIZE parameter specifies the TDS packet size in bytes. The default TDS packet size on most platforms is 512 bytes. If your application is receiving large amounts of text or bulk data across a network, then a larger packet size might significantly improve performance.

The value of packet-size 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. See the Adaptive Server Enterprise System Administration Guide: Volume 1 for more information on network packet size.

If INSERT...LOCATION PACKETSIZE packet-size is not specified or is specified as zero, then the default packet size value for the platform is used.

When INSERT...LOCATION is transferring data between a Sybase IQ server and a remote Sybase IQ or Adaptive Server Enterprise 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 Adaptive Server Enterprise error log might contain more specific information about the cause of the connection failure.

Use the QUOTED_IDENTIFIER parameter to specify the setting of 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};

Use the ISOLATION LEVEL parameter to specify 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

See SQL Anywhere Server - SQL Usage > Using transactions and isolation levels > Isolation levels and consistency.

Note: This reference points to SQL Anywhere documentation.

Sybase IQ does not support the Adaptive Server Enterprise 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 Sybase IQ data types, if Sybase IQ supports the internal conversion. By default, if a remote data column contains over 2GB, Sybase IQ silently truncates the column value to 2GB.

Warning! Sybase IQ does not support the Adaptive Server Enterprise 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. See Unstructured Data Analytics in Sybase IQ.

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, Sybase 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 Sybase IQ loads the correct information.

If you set the LC_ALL environment variable, Sybase IQ uses its value as the locale name. If LC_ALL is not set, Sybase IQ uses the value of the LANG environment variable. If neither variable is set, Sybase IQ uses the default entry in the locales file. For an example, see System Administration Guide: Volume 1 > International Languages and Character Sets > Setting the Locale for an INSERT...LOCATION Statement.

Use the DEFAULT VALUES and VALUES clauses to specify the values to insert. To insert the default column values as specified in the CREATE TABLE statement, specify DEFAULT VALUES. Specifying DEFAULT VALUES is semantically equivalent to specifying this explicit syntax:

INSERT [INTO} <tablename>
VALUES(default, default, ..., default)

where the number of default entries is equal to the number of columns in the table. For example:

INSERT INTO table1 DEFAULT VALUES

You can also use the INSERT VALUES(DEFAULT ...) clause to insert into NULL columns.

The LIMIT option 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.

The NOTIFY option 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.

The SKIP option lets you define a number of rows to skip at the beginning of the input tables for this insert. The default is 0.

The START ROW ID option specifies the record identification number of a row in the IQ table where it should start inserting. By default, new rows are inserted wherever there is space in the table, and each insert starts a new row.

The START ROW ID clause of the LOAD TABLE and the INSERT commands is not allowed on a partitioned table.

For information on the insert-select-load-options WORD SKIP, IGNORE CONSTRAINT, MESSAGE LOG, ROW LOG, and LOG DELIMITED BY and the constrainttype and logwhat parameters, see the LOAD TABLE Statement.

An INSERT on a multicolumn index must include all columns of the index.

Sybase 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.

For more information on the use of column DEFAULT values with inserts, see System Administration Guide: Volume 1 > Data Integrity > Column Defaults Encourage Data Integrity.

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. For more information, see System Administration Guide: Volume 2 > Using Procedures and Batches > Control Statements > Atomic Compound Statements and System Administration Guide: Volume 2 > Using Procedures and Batches > Transactions and Savepoints in Procedures.

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.

See also System Administration Guide: Volume 1 > Data Import and Export > Using the INSERT Statement.

Standards

SQL—ISO/ANSI SQL compliant.
Sybase—Supported by Adaptive Server Enterprise (excluding the insert-load-options).

Permissions

Must have INSERT permission on the table.