Using select into

select into is a two-step operation. The first step creates the new table, and the second step inserts the specified rows into the new table.

Note: You can select into a CIS existing table.

  • Because the rows inserted by select into operations are not logged, select into commands cannot be issued within user-defined transactions, even if the ddl in tran database option is set to true. Page allocations during select into operations are logged, so large select into operations may fill the transaction log.

    If a select into statement fails after creating a new table, the SAP ASE server does not automatically drop the table or deallocate its first data page. This means that any rows inserted on the first page before the error occurred remain on the page. Check the value of the @@error global variable after a select into statement to be sure that no error occurred. Use the drop table statement to remove the new table, then reissue the select into statement.

  • The name of the new table must be unique in the database and must conform to the rules for identifiers. You can also select into temporary tables (see Examples 7, 8, and 11).

  • Any rules, constraints, or defaults associated with the base table are not carried over to the new table. Bind rules or defaults to the new table using sp_bindrule and sp_bindefault.

  • select into does not carry over the base table’s max_rows_per_page value, and it creates the new table with a max_rows_per_page value of 0. Use sp_chgattribute to set the max_rows_per_page value.

  • The select into/bulkcopy/pllsort option must be set to true (by executing sp_dboption) in order to select into a permanent table. You do not have to set the select into/bulkcopy/pllsort option to true in order to select into a temporary table, since the temporary database is never recovered.

    After you have used select into in a database, you must perform a full database dump before you can use the dump transaction command. select into operations log only page allocations and not changes to data rows. Therefore, changes are not recoverable from transaction logs. In this situation, issuing the dump transaction statement produces an error message instructing you to use dump database instead.

    By default, the select into/bulkcopy/pllsort option is set to false in newly created databases. To change the default situation, set this option to true in the model database.

  • select into can be used with an archive database.

  • select into runs more slowly while a dump database is taking place.

  • You can use select into to create a duplicate table with no data by having a false condition in the where clause (see Example 12).

  • You must provide a column heading for any column in the select list that contains an aggregate function or any expression. The use of any constant, arithmetic or character expression, built-in functions, or concatenation in the select list requires a column heading for the affected item. The column heading must be a valid identifier or must be enclosed in quotation marks (see Examples 7 and 8).

  • Datatypes and nullability are implicitly assigned to literal values when select into is used, such as:
    select x = getdate () into mytable

    This results in a non-nullable column, regardless of whether allow nulls by default is on or not. It depends upon how the select commands are used and with what other commands within the syntax.

    The convert syntax allows you to explicitly specify the datatype and nullability of the resulting column, not the default.

    Wrap getdate with a function that does result in a null, such as:

    select x = nullif (getdate (), "1/1/1900") into mytable
    Or, use the convert syntax:
    select x = convert (datetime null, getdate ()) into mytable

  • You cannot use select into inside a user-defined transaction or in the same statement as a compute clause.

  • To select an IDENTITY column into a result table, include the column name (or the syb_identity keyword) in the select statement’s column_list. The new column observes the following rules:

    • If an IDENTITY column is selected more than once, it is defined as NOT NULL in the new table. It does not inherit the IDENTITY property.

    • If an IDENTITY column is selected as part of an expression, the resulting column does not inherit the IDENTITY property. It is created as NULL if any column in the expression allows nulls; otherwise, it is created as NOT NULL.

    • If the select statement contains a group by clause or aggregate function, the resulting column does not inherit the IDENTITY property. Columns that include an aggregate of the IDENTITY column are created NULL; others are NOT NULL.

    • An IDENTITY column that is selected into a table with a union or join does not retain the IDENTITY property. If the table contains the union of the IDENTITY column and a NULL column, the new column is defined as NULL. Otherwise, it is defined as NOT NULL.

  • You cannot use select into to create a new table with multiple IDENTITY columns. If the select statement includes both an existing IDENTITY column and a new IDENTITY specification of the form column_name = identity (precision), the statement fails.

  • If CIS is enabled, and if the into table resides on the SAP ASE server, the SAP ASE server uses bulk copy routines to copy the data into the new table. Before doing a select into with remote tables, set the select into/bulkcopy database option to true.

  • For information about the Embedded SQL command select into host_var_list, see the Open Client Embedded SQL Reference Manual.

Parent topic: select