Inserting from a different database

You can insert data from tables in any accessible database:

Inserting directly from an Adaptive Server Enterprise database

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 between two Sybase IQ databases.

In order to use this capability, all of the following must be true:

StepsInserting data directly from Adaptive Server Enterprise

  1. Connect to both the Adaptive Server Enterprise and the Sybase IQ database using the same user ID and password.

  2. On the Sybase IQ database, issue a statement using this syntax:

    INSERT INTO iq_table 
    LOCATION 'ase_servername.ase_dbname' 
    { SELECT col1, col2, col3,...
    FROM owner.ase_table } 
    
  3. Issue a COMMIT to commit the insert.

When Sybase IQ connects to the remote server, INSERT...LOCATION can also use 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. For more information, complete syntax, and an example, see INSERT statement in Chapter 1, “SQL Statements,” in Reference: Statements and Options.

Loading ASE text and images

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, or a LONG VARCHAR column, 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. Also note that INSERT...LOCATION does not support the use of variables in the SELECT statement. 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.

You may substitute curly braces {} for the single quotation marks that delimit the SELECT statement. (Note that curly braces represent the start and end of an escape sequence in the ODBC standard, and may generate errors in the context of ODBC.)

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 1, “SQL Statements,” in Reference: Statements and Options.

Example

The following command inserts data from the l_shipdate and l_orderkey columns of the lineitem table from the Sybase IQ database iq11db.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.iq11db'
    { SELECT l_shipdate, l_orderkey
    FROM lineitem }

Example

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.iqdb'
	PACKETSIZE 512
	{ SELECT l_shipdate, l_orderkey
    FROM lineitem
    WHERE l_orderkey = 1 }

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

Importing data from pre-Version 12 Sybase IQ

To import data from an Sybase IQ database version earlier than 12.0, you must use one of the following methods:

You cannot use other forms of the INSERT command.

For more information on loading from an older version, see the Installation and Configuration Guide.