Specifying encryption on select into

By default, select into creates a target table without encryption even if the source table has one or more encrypted columns. To encrypt any column in the target table, you must qualify the target column with the encrypt clause, as shown:

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 specifying encryption on a target table are the same as those for encryption specified on create 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.

The following example selects the encrypted column creditcard from the daily_xacts table and stores it in encrypted form in the #bigspenders temporary table:

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