How can I enter values to varchar(max) columns

Using SQL-Server 2008 and concatenating string literals to more than 8000 characters by obvious modification of the following script, I always get the result 8000. Is there a way to tag string literals as varchar(max)?

DECLARE @t TABLE (t开发者_JAVA技巧est varchar(max));

INSERT INTO @t VALUES ( '0123456789012345678901234567890123456789'

+ '0123456789012345678901234567890123456789'

+ '... and 200 times the previous line'

);

select datalength(test) from @t

I used the following code on SQL Server 2008

CREATE TABLE [dbo].[Table_1](
    [first] [int] IDENTITY(1,1) NOT NULL,
    [third] [varchar](max) NOT NULL
) ON [PRIMARY]
END
GO
declare @maxVarchar varchar(max)
set @maxVarchar = (REPLICATE('x', 7199))
set @maxVarchar = @maxVarchar+(REPLICATE('x', 7199))

select LEN(@maxVarchar)

insert table_1( third)
values (@maxVarchar)
select LEN(third), SUBSTRING (REVERSE(third),1,1) from table_1

The value you are inserting in your example is being stored temporally as a varchar(8000) because. To make the insert one will need to use a variable which is varchar(max) and append to it to overcome the internal 8000 limit.

Try casting your value being inserted as a varchar(max):

INSERT INTO @t VALUES (CAST('0123456789012345678901234567890123456789'
+ '0123456789012345678901234567890123456789'
+ '... and 200 times the previous line' AS varchar(max)
);

Also, you may have to concatenate several <8000 length strings (each casted as varchar(max)).

See this MSDN Forum Post.

When I posted the question, I was convinced that there are some limitations for the length or maximal line width of a single string literal to be used in INSERT and UPDATE statement.

This assumption is wrong.

I was led to this impression by the fact the SSMS limits output width for a single column in text mode to 8192 characters and output of PRINT statements to 8000 characters.

Fact is, as far as I know you need only enclose the string with apostrophes and double all embedded apostrophes. I found no restrictions concerning width or total length of a string.

For the opposite task, to convert such strings back from database back to script the best tool I found is ssms toolspack which works for SQL-Server 2005+.