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.
