SQL Copy Text To Varchar

-- copy_text_to_varchar.sql
-- Proc get_text does READTEXT in a loop to read chunks of text
-- no larger than 200, or the column size or @@TEXTSIZE, and
-- produces as many rows as necessary to store the text column
-- as a series of sequenced varchar(200) rows
CREATE PROC get_text @pub_id char(4)
AS

DECLARE @mytextptr varbinary(16), @totalsize int, @lastread int,
    @readsize int
-- Use a TRAN and HOLDLOCK to ensure that textptr and text are 
-- constant during the iterative reads
BEGIN TRAN
SELECT @mytextptr=TEXTPTR(pr_info), @totalsize=DATALENGTH(pr_info),
    @lastread=0,
    -- Set the readsize to the smaller of the @@TEXTSIZE settings,
    -- 200, and the total length of the column
    @readsize=CASE WHEN (200 < DATALENGTH(pr_info)) THEN 200 
    ELSE DATALENGTH(pr_info) END
    FROM pub_info (HOLDLOCK) WHERE pub_id=@pub_id 

-- If debugging, uncomment this to check values 
-- SELECT @mytextptr, @totalsize, @lastread, @readsize

-- Do READTEXT in a loop to get next 200 characters until done
IF @mytextptr IS NOT NULL AND @readsize > 0
    WHILE (@lastread < @totalsize)
    BEGIN
        -- If readsize would go beyond end, adjust readsize
        IF ((@readsize + @lastread) > @totalsize) 
            SELECT @readsize = @totalsize - @lastread 
        
        -- If debugging, uncomment this to check values 
        -- SELECT 'valid ptr?'=textvalid('pub_info.pr_info',
        --    @mytextptr), 'totalsize'=@totalsize, 
        --    'lastread'=@lastread, 'readsize'=@readsize

        READTEXT pub_info.pr_info @mytextptr @lastread @readsize
        IF (@@ERROR <> 0)
            BREAK    -- Break out of loop if an error on read
        -- Change offset to last char read
        SELECT @lastread=@lastread + @readsize 
        
    END
    
 COMMIT TRAN
GO

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects
    WHERE name='##mytmptext' AND type='U')
    DROP TABLE ##mytmptext
GO

-- Intermediate temp table that READTEXT will use.
-- This table is truncated after each pub_id value, so the Identity
-- property sequences the rows for each publisher separately.
CREATE TABLE ##mytmptext
(
    seq_no        int    IDENTITY,
    text_chunk    text
)
GO

IF EXISTS (SELECT * FROM sysobjects
    WHERE name='newprinfo' AND type='U')
    DROP TABLE newprinfo
GO

-- This is the new table that pub_info is copied to.
-- It keeps chunks of text and is sequenced for each pub_id.
CREATE TABLE newprinfo
(
    pub_id        char(4)    NOT NULL,
    seq_no        int        NOT NULL,
    text_chunk    varchar(200),
    CONSTRAINT PK PRIMARY KEY (pub_id, seq_no)
)
GO

-- Having created the procedure get_text, iterate for each pub_id
-- value, temporarily sequencing them in the temp table. Once done
-- for a given pub_id, copy them to the new permanent table. Then 
-- truncate the temp table for use with reseeded Identity for next 
-- pub_id row.
DECLARE @pub_id char(4)
DECLARE iterate_prinfo CURSOR FOR
    SELECT pub_id FROM pub_info ORDER BY pub_id

OPEN iterate_prinfo 
    FETCH NEXT FROM iterate_prinfo INTO @pub_id
        BEGIN 
        TRUNCATE TABLE ##mytmptext

        INSERT ##mytmptext
            EXEC get_text @pub_id

        INSERT newprinfo (pub_id, seq_no, text_chunk)
            SELECT @pub_id, SEQ_NO,
            CONVERT(varchar(200), TEXT_CHUNK) 
            FROM ##mytmptext ORDER BY SEQ_NO

        FETCH NEXT FROM iterate_prinfo INTO @pub_id
        END    

CLOSE iterate_prinfo
DEALLOCATE iterate_prinfo
GO

-- Simply verify contents of the new table
SELECT * FROM newprinfo ORDER BY pub_id,seq_no
GO