I am trying to insert data in a table with column datatype as NTEXT. Ideally it should store more than 8000 characters, but the in my case it is reducing it to 8000 characters.
I am making the Insert Query at runtime in Procedure. Below is the sample query that procedure is making.
INSERT INTO TMPRESULTS SELECT ('A' + ',' + 'B' + ',' + 'C')
A,B,C, etc. are sample data and actual data will be identified at runtime with actual content crossing 8000 characters. Also the variable used to store the value are defined as 'NVARCHAR(MAX)'
However, when I try following query it does insert more than 8000 character in the table
INSERT INTO TMPRESULTS SELECT ('ABCdddd................')
I presume while I am trying to concat the data with '+' sign, sql server is reducing the length to 8000. I can't use CONCAT as data will be more than 256 columns/arguments.
Any idea, why it is doing so? Also, if someone can help with some alternate solution as I will have to make insert query at runtime.
This is documented in + (String Concatenation) (Transact-SQL) - Remarks:
If the result of the concatenation of strings exceeds the limit of 8,000 bytes, the result is truncated. However, if at least one of the strings concatenated is a large value type, truncation does not occur.
For a varchar 8,000 bytes would be 8,000 characters, and for a nvarchar 4,000.
All your literal strings in the query INSERT INTO TMPRESULTS SELECT ('A' + ',' + 'B' + ',' + 'C') are non large value types (In fact, they are all a varchar(1)). If you CONVERT/CAST one of them to a varchar(MAX) this would solve the problem:
INSERT INTO TMPRESULTS
SELECT (CONVERT(varchar(MAX),'A') + ',' + 'B' + ',' + 'C');
if you want an nvarchar, make sure you declare your literal strings as a nvarchar too:
INSERT INTO TMPRESULTS
SELECT (CONVERT(nvarchar(MAX),N'A') + N',' + N'B' + N',' + N'C');