![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, or a selection of rows from another database (Syntax 3).
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 '
[ ENCRYPTED PASSWORD ][ PACKETSIZE packet-size ] ]
... {select-statement}
[ LIMIT number-of-rows ] [ NOTIFY number-of-rows ] [ SKIP number-of-rows ] [ START ROW ID number ]
Add an Eastern Sales department to the database.
INSERT INTO department ( dept_id, dept_name ) VALUES ( 230, 'Eastern Sales' )
Fill 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
Insert 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 }
The INSERT statement is used to add new rows to a database table.
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 is a variation of Syntax 2 that allows you to insert data from an Adaptive Server Enterprise or pre-Version 12 IQ database. The servername.dbname identifies the server and database for the table in the FROM clause. In order 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 this syntax:
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, IQ ignores any database-name you might specify since the only choice is the current database on the local server.
The ENCRYPTED PASSWORD parameter allows you 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 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 may 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 a multiple 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
may contain more specific information about the cause of the connection failure.
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 IQ data types, if 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 upper-case V and the remainder of the letters lower case. 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 non-default 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” of the Sybase IQ System Administration Guide.
The LIMIT option specifies the maximum number of rows you want to insert into the table from a query. The default is 0 for no limit.
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 table(s) 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” of 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
will fail.
An INSERT on a multicolumn index must include all columns of the index.
None.
Must have INSERT permission on the table.
“Using the INSERT statement” in Chapter 7, “Moving Data In and Out of Databases” of the Sybase IQ System Administration Guide
