![INCLUDE statement [ESQL]](arrow-left.gif){kind=icon}
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 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’ }
[ LIMIT number-of-rows ] [ NOTIFY number-of-rows ] [ SKIP number-of-rows ] [ START ROW ID number ]
[ WORD SKIP number ] [ IGNORE CONSTRAINT constrainttype [, …] ] [ MESSAGE LOG ‘string’ ROW LOG ‘string’ [ ONLY LOG logwhat [, …] ] ] [ LOG DELIMITED BY ‘string’ ]
{ CHECK integer | UNIQUE integer | NULL integer | FOREIGN KEY integer | DATA VALUE integer | ALL integer }
{ CHECK | ALL | NULL | UNIQUE | DATA VALUE | FOREIGN KEY | WORD }
[ ENCRYPTED PASSWORD ] [ PACKETSIZE packet-size ] [ QUOTED_IDENTIFIER { ON | OFF } ] [ ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTTED | SERIALIZABLE } ]
Adds an Eastern Sales department to the database:
INSERT INTO Departments (DepartmentID, DepartmentName, DepartmentHeadID) VALUES (600, 'Eastern Sales', 501)
Fills 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
Inserts 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 '
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.
The NUMBER(*) function is
useful for generating primary keys with Syntax 2 of the INSERT statement.
See Chapter
4, “SQL Functions” in Reference: Building Blocks, Tables, and Procedures.
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 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.
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 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 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 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 “Client-Library Topics > Security features > Adaptive Server Enterprise security features > Security handshaking: encrypted password” in the Open Server 15.5 Open Client Client-Library/C Reference Manual.
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.
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.
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 this 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.
Isolation level |
Characteristics |
|---|---|
READ UNCOMMITTED |
|
READ COMMITTED |
|
SERIALIZABLE |
|
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.
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.
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 “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. 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 “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.
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.
None
Must have INSERT permission on the table.
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
