Read TEXT data into a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Read TEXT data into a table

Author

Topic

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-22 : 11:34:51

IF OBJECT_ID(N'pub_info_pr_info_read') IS NOT NULL DROP PROCEDURE pub_info_pr_info_read
IF OBJECT_ID(N'pub_info_pr_info_read_all') IS NOT NULL DROP PROCEDURE pub_info_pr_info_read_all
GO

CREATE PROCEDURE pub_info_pr_info_read (@pointer VARBINARY(16), @offset INT, @length INT) AS
SET NOCOUNT ON
READTEXT pub_info.pr_info @pointer @offset @length
GO

CREATE PROCEDURE pub_info_pr_info_read_all (@pointer VARBINARY(16), @length INT) AS
SET NOCOUNT ON

DECLARE @start INT, @end INT
CREATE TABLE #pub_info_pr_info (Ordinal INT NOT NULL IDENTITY, Value VARCHAR(8000) NOT NULL)

SET @start = 1
SET @end = CASE WHEN @length > 8000 THEN 8000 ELSE @length END

WHILE @start < @length
BEGIN
INSERT INTO #pub_info_pr_info (Value)
EXEC pub_info_pr_info_read @pointer, @start, @end

SET @start = @start + 8000
SET @end = CASE WHEN @start + 8000 > @length THEN @length - @start ELSE 8000 END
END

SELECT * FROM #pub_info_pr_info
DROP TABLE #pub_info_pr_info
GO

DECLARE @pointer varbinary(16), @length INT
SELECT @pointer = TEXTPTR(pr_info), @length = DATALENGTH(pr_info)
FROM pub_info pr INNER JOIN publishers p ON pr.pub_id = p.pub_id AND p.pub_name = 'New Moon Books'

EXEC pub_info_pr_info_read_all @pointer, @length

...
you could then create your own temp and just execute the last procedure and then manipulate your info anyway you want or use these to create something cool ... don't be a fraid of text data any longer!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources