0

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.

vnkotak
  • 129
  • 4
  • 14
  • 1
    The type of an expression is given by its members, not by the column/variable you will store the result in. If no members of the expression are of type `nvarchar(max)`, the entire expression will not be promoted to `nvarchar(max)` when it calculates, even if the resulting length would be greater than 4000/8000. Cast at least one of the expression members to `nvarchar(max)`. – GSerg Apr 16 '19 at 09:02
  • HI @GSerg, I have stored the data in NVARCHAR(MAX) which makes up the experssion. Don't know if I should do anything else – vnkotak Apr 16 '19 at 09:03
  • `ntext` has been deprecated for over a *decade*. Beyond time to have upgraded/replaced it. – Damien_The_Unbeliever Apr 16 '19 at 09:45

2 Answers2

5

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');
Thom A
  • 88,727
  • 11
  • 45
  • 75
0

In SQL Server 2017 onwards, there is CONCAT_WS function to perform concatenation easily. You can also read about CONCAT

So, instead of this: 

INSERT INTO TMPRESULTS SELECT ('A' + ',' + 'B' + ',' + 'C')

We can have below: 

INSERT INTO TMPRESULTS SELECT CONCAT_WS(CAST(N',' AS NVARCHAR(MAX)),'A','B','C'))

I have put sample below from SQL Server 2017 for reference:

CREATE TABLE #tempValue(BigValue NVARCHAR(MAX))

INSERT INTO #tempValue
SELECT CONCAT_WS(CAST(N',' AS NVARCHAR(MAX)),REPLICATE('A',4000),REPLICATE('B',4000),REPLICATE('C',4000))

SELECT LEN(BigValue) FROM #tempValue -- 12002

Also, CONCAT_WS is better for below reasons:

If CONCAT_WS receives arguments with all NULL values, it will return an empty string of type varchar(1).

CONCAT_WS ignores null values during concatenation, and does not add the separator between null values.

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
  • While using `concat_ws` may look cleaner, it has nothing to do with the truncation problem. `select cast(CONCAT_WS(',', replicate('A', 4000), replicate('B', 4000), replicate('C', 4000)) as nvarchar(max))` will still truncate the result at 8000 characters. – GSerg Apr 16 '19 at 10:55
  • @GSerg, Thanks. I have updated my answer. If one of the parameters is NVARCHAR(MAX), CONCAT_WS automatically converts the return type as NVARCHAR(MAX) – Venkataraman R Apr 16 '19 at 11:08