Updating a ntext colum with ntext from another table
-
Hi,
I'm having trouble understanding how I might set the data in an ntext column based on the value of another column of type ntext.
Looking at the syntax for writetext and updatetext the only way to pass data to these functions is via a variable or via text entered in single quotes.
A stored procedure does not allow you to declare variables of type text or ntext so my idea was to provide WriteText with a subselect as such :-
SELECT @ptrval = TEXTPTR(onlytext)
FROM #tempo_results
WRITETEXT #tempo_results.onlytext @ptrval ( SELECT SUBSTRING ( htmlandtext, @endbracket, @startbracket ) FROM #tempo_results )
... of course this doesn't work.
Any advice would be much appreciated .
Nigel
-
Believe it or not but SQL server handles well just simple insert/update statement to insert/update text field selected from another table, at least it works like a charm with datalength = 1244306.
Julia
-
I think the answer you are looking for, is that you need to use a cross join.
Like so:
CREATE TABLE #projects (id int NOT NULL,
description ntext NULL)
go
CREATE TABLE #t (id int NOT NULL,
descr ntext NOT NULL)
go
INSERT #projects (id) VALUES(21)
INSERT #t(id, descr) VALUES (1, replicate('ABCD', 1000))
go
UPDATE #projects
SET description = t.descr
FROM #projects p
CROSS JOIN #t t
WHERE t.id = 1
AND p.id = 21
go
SELECT * FROM #projects
go
DROP TABLE #projects, #t
That will then allow you to update an ntext in one table from an ntext in another
Of course, don't forget.... if you're using SQL2005 you can now use the wonderful nvarchar(MAX) which works just like an nvarchar but can store as much as a ntext.
Cheers
Coll
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply