CODEWITHSUNDEEP
sqlsql-servert-sql
Alex Finelt
Alex Finelt

Reputation: 113

SQL: trying to insert large string into varchar(max)

i am trying to do an 

INSERT INTO MYTBL
VALUES (CAST(SPACE(7997) + 'abcd' as nvarchar(max))

and it does not go over 8000 bytes not sure what it is i am doing wrong

MYTBL only has one column (Col1 (nvarchar(max))

Upvotes: 1

Views: 8113

Answers (3)

Steve Kass
Steve Kass

Reputation: 7184

The string you are casting is SPACE(7997) + 'abcd'. This string is a varchar(8000) value of [7997 spaces]abc.

The concatenation is evaluated before the CAST operation is applied. CAST will change the type of the result of the concatenation, but surrounding the concatenation expression with CAST won't magically change how the + operation is evaluated.

SQL is a strongly typed language, and every expression must have a well-defined type that depends on the types of the operands, not on the values of the operands, and not on how the expression is used in a larger context.

The type of SPACE(7997) + 'abcd' is the result type of [varchar(7997) + varchar(4)], which is varchar(8000), and truncation takes place.

Here's an analogous situation.

-- The `SELECT` fails, because the product of 2 and an `INT`has type `INT`
-- When `@i = 2000000000`, there is overflow.
declare @i int;
set @i = 2000000000;
select 2*@i;

-- The following also fails, for exactly the same reason, even though `BIGINT`
-- could hold the result.
declare @i int;
set @i = 2000000000;
select CAST(2*@i AS BIGINT);

-- This, however, succeeds, because now the type of the product is `BIGINT` 
declare @i int;
set @i = 2000000000;
select CAST(2 AS BIGINT)*@i;

Upvotes: 1

user275683
user275683

Reputation:

based on your code you are missing last ")"

modify your code to this:

INSERT INTO MYTBL
VALUES (CAST(SPACE(7997) + 'abcd' as nvarchar(max)))

additionaly just like @datagod said you need to convert/cast your strings as NVARCHAR(max) before concatenation.

you can use this 

INSERT INTO MYTBL
    VALUES ( CONVERT(NVARCHAR(MAX), SPACE(7997)) + CONVERT(NVARCHAR(MAX), 'abcd') )

Upvotes: 3

datagod
datagod

Reputation: 1051

Try casting each of your strings as nvarchar(max) first, then concatenate them. I have found that string functions such as replicate() return regular varchar results which get truncated after 8000 chars.

Upvotes: 2

Related Questions