469,270 Members | 1,214 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

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

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

I tried this

UPDATE [projects]

SET [description] = (SELECT [description_ntext] 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."

Aug 29 '06 #1
2 2589
Igor (je*******@gmail.com) writes:
Is there a way to transfer ntext data from one table to another?

I tried this

UPDATE [projects]

SET [description] = (SELECT [description_ntext] 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****@sommarskog.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
Aug 29 '06 #2
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****@sommarskog.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
Aug 30 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Anand | last post: by
1 post views Thread by Derek Erb | last post: by
2 posts views Thread by Sileesh | last post: by
4 posts views Thread by Cylix | last post: by
3 posts views Thread by =?Utf-8?B?ZGF2aWQ=?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.