473,320 Members | 2,024 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,320 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 2733
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: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.