Using ReadText To Insert - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Using ReadText To Insert

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-10 : 08:10:38

Bob writes "Problem:
I would like to Insert into temp table using the READTEXT command from a text field. I can't use substring because string may go past 8000 or start after the first 8000.

Version:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Code:
declare @newptr varbinary(16)
--contentsoftalk is a text field
select @newptr =textptr(contentsoftalk) from bp where id = 5

Create Table #sen (Sentence text )

---This part won't make it past "check syntax" Syntax error at convert. @start and @finish are int fields set in a cursor

INSERT #sen (sentence) EXECUTE ('READTEXT bp.contentsoftalk ' + convert(varchar(50),@newptr) +' '+ convert(varchar(50),@start)+' '+ convert(varchar(50),@finish))

---If I hard code the values in like this it works
insert #sen EXECUTE ('READTEXT bp.contentsoftalk 0xFBFFFDAE000000005A01000001000200 9753 20')

I don't know if you find this hard but I can't seem to find an answer anywhere.
Thanks in advance,
Bob"

javamick
Starting Member

7 Posts

Posted - 2004-06-11 : 17:14:50

I think you can accomplish what you want without dynamic SQL. I think you have to insert a value into the table though so you can get a pointer to the location. There may be a better way, but try this:

DECLARE @SENID int
declare @newptr varbinary(16)
declare @senptr varbinary(16)

select @newptr =textptr(contentsoftalk) from bp where id = 5

CREATE TABLE #sen (ID int IDENTITY(1,1),Sentence text )
INSERT INTO #sen (Sentence) VALUES ('')
SET @SENID = SCOPE_IDENTITY()

SELECT @senptr = textptr(Sentence) FROM #sen WHERE ID = @SENID
UPDATETEXT #sen.Sentence @senptr NULL 0 bp.contentsoftalk @newptr

SELECT * FROM #sen -- or do a READTEXT to get everything in the text field.
DROP TABLE #sen

--
Micky McQuade
www.mcquade.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources