You can insert data from tables in any accessible database:
Tables in either the IQ Store or the Catalog Store of the database you are currently connected to.
Tables in an Adaptive Server Enterprise database.
A proxy table in your current database, that corresponds to a table in a database on a remote server. For details, see Chapter 16, “Accessing Remote Data” and Chapter 17, “Server Classes for Remote Data Access.”
You can insert data easily from an Adaptive Server Enterprise or SQL Server database, using the LOCATION syntax of the INSERT statement. You can also use this method to move selected columns from a pre-Version 12 Sybase IQ database into a Version 12.x database.
In order to use this capability, all of the following must be true:
The Sybase connectivity libraries must be installed on your system, and the load library path environment variable for your platform must point to them.
The Adaptive Server Enterprise server to which you are connecting must exist in the interfaces file on the local machine.
You must have read permission on the source ASE or pre-Version 12 IQ database, and INSERT permission on the target IQ 12.x database
Inserting data directly from Adaptive Server Enterprise
Connect to both the Adaptive Server Enterprise and the Sybase IQ 12.x database using the same user ID and password.
On the Sybase IQ 12.x database, issue a statement using this syntax:
INSERT INTO asiq_table
LOCATION 'ase_servername.ase_dbname'
{ SELECT col1, col2, col3,...
FROM owner.ase_table }
Issue a COMMIT to commit the insert.
Sybase IQ does not support the Adaptive Server Enterprise data type TEXT, but you can execute INSERT...LOCATION from an ASE database column of data type TEXT. Also note that INSERT...LOCATION does not support the use of variables in the SELECT statement. Text and image data inserted is right truncated at 32767 bytes.
If you need to load larger data, see “Bulk loading data using the LOAD TABLE statement”.
For details on the syntax of the INSERT statement, see Chapter 6, “SQL Statements,” of the Sybase IQ Reference Manual.
The following command inserts data from the l_shipdate and l_orderkey columns of the lineitem table from the Sybase IQ 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.asiq11db'
{ SELECT l_shipdate, l_orderkey
FROM lineitem }
The destination and source columns may have different names.
The order in which you specify the columns is important, because data from the first source column named is inserted into the first target column named, and so on.
You can use the predicates of the SELECT statement within the INSERT command to insert data from only certain rows in the table.
This example inserts the same columns as the previous example, but only for the rows where the value of l_orderkey is 1. Also in this example, the TDS packet size is specified as 512 bytes.
INSERT INTO lineitem
(l_shipdate, l_orderkey)
LOCATION 'detroit.asiqdb'
PACKETSIZE 512
{ SELECT l_shipdate, l_orderkey
FROM lineitem
WHERE l_orderkey = 1 }
If you use START
ROW ID and you select fewer columns than exist in the
destination table, the columns in remaining rows of the destination
table will be NULLs, if NULLs are legal values. See “Partial-width insertions” for more information.
To import data from an Sybase IQ database version earlier than 12.0, you must use one of the following methods:
The LOAD TABLE command with the UNLOAD FORMAT option
The INSERT...LOCATION syntax
You cannot use other forms of the INSERT command.
For more information on loading from an older version, see the Sybase IQ Installation and Configuration Guide.
