How to update SQL TEXT or NTEXT from VARCHAR or NVARCHAR in SQL 2005

With SQL Server 2005 a new concept was introduced ... varchar(MAX) and nvarchar(MAX).  This allows the rich features of varchar functions without the practical limits from previous SQL versions.  I ran across this when I was trying to secure fields in a table with a view and then insert into a TEXT colum in the view.  In my scenario, I did want to have access to the whole table just the x fields in question.  UPDATETEXT and APPENDTEXT did not work as expected and I did not want to create temp tables.  I finally landed on a solution that involves the new MAX varchar/nvarchar feature.

For example, if you want to append text to the begging of a running note you could use the following code:

DECLARE @CommentVar varchar(MAX);

-- Read in existing comments from COMMENTS column in view named MyView

SELECT @CommentVar = COMMENTS FROM MyView WHERE KeyId=1;

-- Insert text a top of comment (example is a running history with most recent first)

SET @CommentVar = 'Note on ' + CONVERT(Varchar,GETDATE(),100) + CHAR(13)+ CHAR(10) + 
   'This is my note.' + CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10) + @CommentVar;

-- Now go update the field

UPDATE MyView SET COMMENTS = @CommentVar WHERE KeyId=1;

Just remember that this is a new feature of SQL 2005 and is not compatible with previous versions.