LISTING 2: Pseudocode to Add Text with Updatetext

DECLARE @ptrval varbinary(16)
DECLARE @myincidentid INT
DECLARE @i int
DECLARE @data varchar(255)
DECLARE @beginoffset
-- DECLARE AND OPEN A CURSOR ON oldtable now
While @@fetch_status <> -1  -- more rows from oldtable to get
    begin
    Select @i = 1
    Select @beginoffset = 1
    While @i < 20
        begin
           SELECT @length = -- FIGURE OUT length of varchar(255) column you want - probably need a dynamic exec here
           Select @data = exec ('Select vchworknote' + i + ' from oldtable where iincidentid = ' + @myincidentid)
            SELECT @ptrval = TEXTPTR(worknotetext)  -- get the text pointer for the table
            FROM newtable WHERE iincidentid = @myincidentid
            UPDATETEXT mynewtable.worknotetext @ptrval @beginoffset NULL @data -- update the data
            SELECT @i = @i + 1 --update the counter
            SELECT @beginoffset = @beginoffset + @length - 1 -- so that the beginning offset will be correct for next data
        end

    fetch next from mycursor into @myincidentid
    end