![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) or lets users specify the setting for options at the remote server.
Syntax 1
INSERT [ INTO ] [ owner.]table-name [ ( column-name [, ...] ) ] ... VALUES ( expression ... )
Syntax 2
INSERT [ INTO ] [ owner.]table-name [ ( column-name [, ...] ) ] ... insert-load-options ... select-statement
Syntax 3
INSERT [INTO] [owner.]table-name[ (column-name [, ... ] ) ]
... insert-load-options
[ LOCATION 'servername.dbname'
[ location-options ] ]
... { select-statement }
[ LIMIT number-of-rows ] [ NOTIFY number-of-rows ] [ SKIP number-of-rows ] [ START ROW ID number ]
[ ENCRYPTED PASSWORD ] [ PACKETSIZE packet-size ] [ QUOTED_IDENTIFIER { ON | OFF } ]
Adds an Eastern Sales department to the database:
INSERT INTO department ( dept_id,
dept_name )
VALUES ( 230, 'Eastern Sales' )
Fills the table dept_head with the names of department heads and their departments:
INSERT INTO dept_head (name,
dept)
NOTIFY 20
SELECT emp_fname || '
' || emp_fname
AS name,
dept_name
FROM employee JOIN department
ON emp_id = dept_head_id
Inserts data from the l_shipdate and l_orderkey columns of the lineitem table from the Sybase IQ 11.5 database asiq11db.dba on the server detroit, into the corresponding columns of the lineitem table in the current database:
INSERT INTO lineitem
(l_shipdate, l_orderkey)
LOCATION 'detroit.asiqdb'
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 do mass insertion into a table with 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 5, “SQL Functions”).
Syntax 3 INSERT...LOCATION is a variation of Syntax 2 that lets you insert data from an Adaptive Server Enterprise or Sybase IQ database. The servername.dbname identifies the server and database for the table in the FROM clause. The SELECT statement might 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.) To use Syntax 3, the Adaptive Server Enterprise server to which you are connecting must exist in the interfaces file on the local machine.
The following Open Client restrictions apply to queries using Syntax 3:
You can insert a maximum of 2147483647 rows.
You cannot use unsigned integer data.
Sybase IQ connects to the server and database you specify and returns the results from the queries in those tables to insert in the current database. If you omit the server-name, Sybase IQ ignores any database-name you might 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.
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.
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 the following example using ‘c1’:
INSERT INTO foo
LOCATION 'ase.database'
QUOTED_IDENTIFIER ON {select "c1" from xxx};
While you are connected by INSERT...LOCATION, the IQ hostname and the program_name Sybase IQ appear in sysprocesses in the Adaptive Server Enterprise master database.
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. All data inserted is silently right truncated at 32767 bytes.
If you use INSERT...LOCATION to insert
data selected from a VARBINARY column, set the LOAD_MEMORY_MB option
on the local database to limit memory used
by the insert, and 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 Sybase IQ System Administration Guide.
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 Sybase IQ System Administration Guide.
Use the START ROW ID option for partial-width
inserts only. If the columns being loaded already contain data,
the insert fails.
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 Sybase IQ System Administration Guide.
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 Sybase IQ System Administration Guide
