Updating text, ntext values
While working on import utility we faced problem of maintaining big string values. We had a table with ntext field. In a stored procedure you can not declare local variable with text or ntext data types. So it was not possible to maintain big strings in stored procedure. To overcome this problem we used temporary table and used the UPDATETEXT.
See sample code.
create table t1(MyStr text)
insert into t1 values('aaaa')
CREATE TABLE #mytemp (myf text)
Insert into #mytemp values ('mayur')
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(myf) from #mytemp
UPDATETEXT #mytemp.myf @ptrval NULL 0 'End statement' ---Append at the end
UPDATETEXT #mytemp.myf @ptrval 0 0 'some more test string' ---Insert at the beginning
select #mytemp.myf from #mytemp
Update t1 set mystr = myf from #mytemp
select * from t1
drop table #mytemp
See sample code.
create table t1(MyStr text)
insert into t1 values('aaaa')
CREATE TABLE #mytemp (myf text)
Insert into #mytemp values ('mayur')
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(myf) from #mytemp
UPDATETEXT #mytemp.myf @ptrval NULL 0 'End statement' ---Append at the end
UPDATETEXT #mytemp.myf @ptrval 0 0 'some more test string' ---Insert at the beginning
select #mytemp.myf from #mytemp
Update t1 set mystr = myf from #mytemp
select * from t1
drop table #mytemp
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home