CODEWITHSUNDEEP
sql-serversql-server-2016
Kshitij
Kshitij

Reputation: 37

Inserting more than 32000 characters data in Text column

I have a table with a column data type text. i want to insert data in this column using SSMS(can use other tools also) with insert statement. 

Update dbo.xyz set data = 'query of length 31232 characters' where id = 1

How can i insert it When i do it through SSMS only first 8000 Characters are inserted.

Upvotes: 0

Views: 3087

Answers (2)

sepupic
sepupic

Reputation: 8687

I think you have a problem with your 'query of length 31232 characters', I give you an example.

I create the table xyz similar to yours with id and a column Col of datatype text.

I then inserted 2 rows and made 2 updates to it, note that in one case I update with replicate('a', 32000) and in other with replicate(cast('a' as varchar(max)), 32000), in the first case only 8000 characters were inserted, but it's not INSERT problem.

The problem here is that if you don't explicitely cast to varchar(max) your string is truncated to 8000 characters.

create table dbo.xyz(id int, col text);

insert into dbo.xyz(id) values(1), (2);

Update dbo.xyz 
set col = replicate('a', 32000) 
where id = 1;

Update dbo.xyz 
set col = replicate(cast('a' as varchar(max)), 32000) 
where id = 2;

select id, len(cast(col as varchar(max)))
from dbo.xyz;

enter image description here

Upvotes: 1

Eralper
Eralper

Reputation: 6612

You can create VARCHAR(MAX) or NVARCHAR(MAX) data types which enables data storage up to 2 GB

Upvotes: 0

Related Questions