Jerry Nixon @Work: Can you alter/append a text field in SQL 2005? Yes.

Jerry Nixon on Windows

Friday, March 14, 2008

Can you alter/append a text field in SQL 2005? Yes.

Here's how you do it (using .WRITE):

/*
.WRITE in the update clause like substring
*/

CREATE TABLE TempDemoTable (BigCol varchar(max))
Insert into TempDemoTable (BigCol)
Select 'Now is the time for all good men to come to the aid of their country';
Select * from TempDemoTable;

-- note: partial updates to large value data types using the .WRITE clause are minimally logged

Update TempDemoTable
Set BigCol .WRITE ('women', 29, 3);
Select * from TempDemoTable;

-- length is null

Update TempDemoTable
Set BigCol .WRITE ('women', 29, null);
Select * from TempDemoTable;

-- expression is null

Update TempDemoTable
Set BigCol .WRITE (null, 29, 3);
Select * from TempDemoTable;

-- offset is null

Update TempDemoTable
Set BigCol .WRITE (getdate(), null, null);
Select * from TempDemoTable;

DROP TABLE TempDemoTable