INSERT statement

INSERT [ INTO ] [ owner.]table-name [ ( column-name [, …] ) ]
... { DEFAULT VALUES | VALUES ( [ expression | DEFAULT,… ) ] }

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

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

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 COMMITTTED | SERIALIZABLE } ]

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

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

INSERT INTO lineitem
  (l_shipdate, l_orderkey)
  LOCATION 'detroit.iqdet'
  PACKETSIZE 512
  ' SELECT l_shipdate, l_orderkey
FROM lineitem '

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. (Curly braces represent the start and end of an escape sequence in the ODBC standard, and might generate errors in the context of ODBC.)

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

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.

For example, user russid connects to the Sybase IQ database and executes the following 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 follows:

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

The external login is defined on the IQ server as follows:

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.

The ENCRYPTED PASSWORD parameter lets you 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 does not support this password encryption.

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 equal to 512 bytes, even if you specify a different value for PACKETSIZE.

The QUOTED_IDENTIFIER parameter lets you 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 the following example using ‘c1’:

INSERT INTO foo
LOCATION 'ase.database'
QUOTED_IDENTIFIER ON {select "c1" from xxx};

The ISOLATION LEVEL parameter allows you to specify an isolation level for the connection to a remote server.

For more information on isolation levels, see “Isolation levels and consistency” in SQL Anywhere Server – SQL Usage > Creating Databases > Using transactions and isolation levels.

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.

Users must be specifically licensed to use the Large Objects Management functionality. For details on the Large Objects Management option, see Large Objects Management in Sybase IQ.

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 “Setting locales” in Chapter 11, “International Languages and Character Sets” in the System Administration Guide: Volume 1.

The DEFAULT VALUES and VALUES clauses allow you to specify the values to insert. If you want to insert the default column values as specified in the CREATE TABLE statement, specify DEFAULT VALUES. Specifying DEFAULT VALUES is semantically equivalent to specifying the following 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.

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. This option is used for partial-width inserts, which are inserts into a subset of the columns in the table. By default, new rows are inserted wherever there is space in the table, and each insert starts a new row. Partial-width inserts need to start at an existing row. They also need to insert data from the source table into the destination table positionally by column, so you must specify the destination columns in the same order as their corresponding source columns. The default is 0. For more information about partial-width inserts, see Chapter 7, “Moving Data In and Out of Databases” in the System Administration Guide: Volume 1.

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 “Using column defaults” in Chapter 9, “Ensuring Data Integrity” in the System Administration Guide: Volume 1.

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 “Atomic compound statements” and “Transactions and savepoints in procedures” in Chapter 1, “Using Procedures and Batches” of the System Administration Guide: Volume 2.

Side effects

• SQL92 Entry-level feature.

• Sybase Supported by Adaptive Server Enterprise (excluding the insert-load-options).

DELETE statement

LOAD TABLE statement

SYNCHRONIZE JOIN INDEX statement

“Using the INSERT statement” in Chapter 7, “Moving Data In and Out of Databases” in the System Administration Guide: Volume 1

---

Copyright © 2009. Sybase Inc. All rights reserved.

View this document as PDF