By default, select into creates a target table without encryption even if the source table has one or more encrypted columns. select into requires column-level permissions, including decrypt, on the source table.
Encrypt columns on the new table by using:
select [all|distinct] column_list into table_name [(colname encrypt [with [[[database.][owner].]keyname]] [, colname encrypt [with[[[database.][owner].]keyname]])] from table_name | view_name
You can encrypt a specific column in the target table even if the data was not encrypted in the source table. If the column in the source table is encrypted with the same key specified for the target column, Adaptive Server optimizes processing by bypassing the decryption step on the source table and the encryption step on the target table.
The rules for encryption on a target table are the same as those for the encrypt specifier in create table on the source table in regard to:
Allowable datatypes on the columns to be encrypted
The use of the database default key when the keyname is omitted
The requirement for select permission on the key used to encrypt the target columns.
For example, to encrypt the creditcard column, enter:
select creditcard, custid, sum(amount) into #bigspenders (creditcard encrypt with cust.dbo.new_cc_key) from daily_xacts group by creditcard having sum(amount) > $5000
