select into requires column-level permissions, including decrypt, on the source table. The following partial syntax for select into includes only clauses specific to encryption. See the Reference Manual for the complete syntax.
select [all|distinct] column_list into target_table [(colname encrypt [with [database.[owner].]keyname] [,colname encrypt [with [database.[owner].]keyname]])] from tabname | viewname
This example encrypts the creditcard column in the bigspenders table:
select creditcard, custid, sum(amount) into #bigspenders (creditcard encrypt with custdb.dbo.cc_key) from daily_xacts group by creditcard having sum(amount) > $5000
If you use the encrypt clause without specifying a key name, Adaptive Server uses the database default key to encrypt the data in the target column.
If a column in the source table is encrypted and you do not specify the encrypt clause for the target column, Adaptive Server decrypts the data in the source table and inserts plain text data in the target column.
If you specify encryption for the target column with the same key used for the source column data, and if the key does not use an initialization vector or random padding, then Adaptive Server copies the data from the source column to the target column as cipher text, without intermediate decryption and reencryption operations.
If however, you specify encryption for the target column using a different key from that used for the source column, or if the key uses an initialization vector or padding during encryption, the Adaptive Server performs a decryption and encryption operation for each selected row of the encrypted column.
