joejuska (7/30/2016)
No I DO NOT WANT TO HASH ONLY THE FIRST 50 CHARACTERS. I WANT TO HASH THE ENTIRE FILE BUT HAVE THE RESULTANT FIELD NVARCHAR(50) OR CHAR(50) INSTEAD OF SSMS MAKING THE RESULTANT FIELD NVARCHAR(MAX)
Instead of shouting at people who are trying to help, you might want to try actually paying attention to what they say.
The error you see is not caused by the insert you are doing (easy to see if you just run the SELECT without the INSERT). It is caused by the call to the HASHBYTES function itself. Eirikur already told you that in his first contribution to this thread. The input for the HASHBYTES function cannot exceed 4000 characters for unicode or 8000 character for single-byte string data. Easy to veryfy:
DECLARE @x nvarchar(max);
SET @x = REPLICATE (CAST('x' AS varchar(max)), 4001);
SELECT HASHBYTES('MD5', LEFT(@x, 4000)); -- Returns a 16-byte binary
SELECT HASHBYTES('MD5', @x); -- Error
The fix? Either follow one of the options Erikur posted (1: Use CLR; 2: Build T-SQL logic to chop the string, concatenate the hases, rinse and repeat) or accept that it cannot be done and hash only the first 4000 characters.