Is there a way to transfer ntext data from one table to another? MSSQL2000

[Igor]

Is there a way to transfer ntext data from one table to another?

I tried this

UPDATE [projects]

SET [description] = (SELECT [description_nte xt] FROM [table] WHERE
[id]=1)

WHERE [id_project] = 1;
and this

DECLARE @DESCRIPTION ntext

SET @DESCRIPTION = (SELECT [bids].[bid_conditions] FROM [bids],
[projects] WHERE [bid_accepted_id] = [bids].[id_bid] AND [id_project] =
@ID_PROJECT);

UPDATE [projects]

SET [description] = @DESCRIPTION

WHERE [id_project] = 1;

none of those work in MSSQL2K,
error reported is "The text, ntext, and image data types are invalid
for local variables."

[Erland Sommarskog]

Igor (je*******@gmai l.com) writes:

Is there a way to transfer ntext data from one table to another?

I tried this

UPDATE [projects]

SET [description] = (SELECT [description_nte xt] FROM [table] WHERE
[id]=1)

WHERE [id_project] = 1;
...

It appears that you have to use the JOIN syntax, as in this example:

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

Note that if you are on SQL 2005, there is no reason to struggle with
ntext. Use nvarchar(MAX) instead, which is a first-class cititez, but
can fit just as much data as ntext.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Igor]

OK thank you, I will try your solution. Unfortunately i'm limited to
MSSQL 2000 but thank you for you suggestion.

Erland Sommarskog wrote:

>
It appears that you have to use the JOIN syntax, as in this example:

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

Note that if you are on SQL 2005, there is no reason to struggle with
ntext. Use nvarchar(MAX) instead, which is a first-class cititez, but
can fit just as much data as ntext.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx