473,224 Members | 1,693 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

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 2727
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Anand | last post by:
If I insert 8000 byte of data, in the one of the column. now I want to read the data, is there any to see all the data, through select statement?
5
by: Cally | last post by:
Hello, I would like to convert a field from ntext field found in one database table to float field found in another database table. The reason why I want to do this is a long one. I have...
1
by: Derek Erb | last post by:
SQL Server 2000 : I have a series of tables which all have the same structure. When any of these tables are modified I need to syncrhonise all of those modifications with one other table wich is a...
2
by: Sileesh | last post by:
HI I know this is not the right forum to post this question, but i think some one might have a suggestion. I have a Table "Test" with columns Id bigint (PK), Number Varchar(50), Notes ntext....
4
by: Cylix | last post by:
Is there any difference between a nText field value='' and value=NULL ?
3
by: =?Utf-8?B?ZGF2aWQ=?= | last post by:
I try to follow Steve's paper to build a database, and store a small text file into SQL Server database and retrieve it later. Only difference between my table and Steve's table is that I use NTEXT...
0
by: comp21 | last post by:
Hi, Now, I have retrieved or imported data from excel spreadsheet to vb6 application. Now I want to compare this part of data with the one already existing in mssql2000 server(table name being...
0
by: sko | last post by:
I have created an access data project in acces 2003 and have SQL Server 2003 on my client computer. When I try to transfer the database to Windows SQL Server on our main server to start business...
6
by: curly | last post by:
Hi, I have a log table where a date time is stored in an ntext field. I need to take this date time and use it to search for other records logged at that date time. I've tried using a...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.