SQL Truncation Issue Converting VARCHAR to VARBINARY

Question

I have a fairly simple insert from a csv file into a temp table into a table with an encrypted column.

CREATE TABLE table1
(number varchar(32) NOT NULL
, user_varchar1 varchar(65) NOT NULL
, account varchar(32) NOT NULL)

CREATE TABLE #temp1
(number varchar(32) NOT NULL
, user_varchar1 varchar(65) NOT NULL
, account varchar(32) NOT NULL)


OPEN SYMMETRIC KEY SKey                    
DECRYPTION BY CERTIFICATE CERTCERT 
--Flat File Insert
BULK INSERT #temp1 
FROM '\\Server\Data\filename.csv'
WITH (FIELDTERMINATOR = ','
, FIRSTROW =2
, ROWTERMINATOR = '\n'
);

INSERT INTO table1
(number, user_varchar1, account_encrypted)
SELECT user_varchar1, number
, ENCRYPTBYKEY(KEY_GUID('SKey'),(CONVERT(varbinary(MAX), account)))
FROM #temp1

--SELECT * FROM #esa_import_ach
DROP TABLE #temp1
SELECT * FROM table1

CLOSE MASTER KEY
CLOSE SYMMETRIC KEY SKey;

The error I receive is

Msg 8152, Level 16, State 11, Line 40
String or binary data would be truncated.

Now if I allow NULLS into table1, it fills with NULLS, obviously. If I omit the account_encrypted column altogether, the script works.

If I use

INSERT INTO table1 (number, user_varchar1, account) 
VALUES ('175395', '87450018RS', ENCRYPTBYKEY(KEY_GUID('SKey'), (CONVERT(varbinary(MAX), account)))

there's no problem.

So, is there something wrong with the way I'm executing the BULK INSERT, is it my declaration of the data types or is it the source file itself.

The source file looks like this (just one row):

emp_id, number, account
175395, 87450018RS,GRDI27562**CRLF**

Thanks and I'm hoping this makes sense.

Answer 1

The problem is that your account column is defined as varchar(32).

ENCRYPTBYKEY returns a result with a max size of 8000. That just won't fit in your column. Either expand the column, or cast the result to a smaller size to fit it inside the column. Right now it just won't fit.