COPY ALL THE ROW'S VALUE ONLY FROM EXISTING COLUMN (NVARCHAR) TO ANOTHER EXISTING COLUMN (NTEXT) IN SQL 2005

Question

Post reply

COPY ALL THE ROW'S VALUE ONLY FROM EXISTING COLUMN (NVARCHAR) TO ANOTHER EXISTING COLUMN (NTEXT) IN SQL 2005

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 739545 More actions · Answer 1

If you have this table:

create table mytable

( mychar nvarchar(4000)

, mytext ntext

)

then I can do this:

UPDATE dbo.mytable

SET mytext = mychar + CAST( mytext AS NVARCHAR(MAX))

That will concatenate the values of the tables. You can't do this with text/ntext, but you can cast it. FYI, you should not have text columns anymore. SQL 2005 supports varchar(max) and you ought to convert your columns to this type (or nvarchar(max))

grahamrd Valued Member Points: 54 More actions · Answer 2

Hi Mr. Jones,

Thank You very much for quick reply.

Yes I have change EXTENDEDDESCRIPTION (NVARCHAR(MAX) USING SQL Server Management Studio

Now I need to copy all the value from DESCRIPTION(nvarchar) column to EXTENDEDDESCRIPTION(nvarchar (Max) in the same table and same database.

My DB name is BHOLENATH

my table name is ITEM

My ONE column name is DESCRIPTION (I want to copy all value from this column to EXTENDEDDESCRIPTION)

My second column name is EXTENDEDDESCRIPTION (this column currently has some value and I want to keep that value and more add from DESCRIPTION column.

Thank you Mr. Jones for your help.

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

grahamrd (9/24/2011)
Hi Mr. Jones,
Thank You very much for quick reply.
Yes I have change EXTENDEDDESCRIPTION (NVARCHAR(MAX) USING SQL Server Management Studio
Now I need to copy all the value from DESCRIPTION(nvarchar) column to EXTENDEDDESCRIPTION(nvarchar (Max) in the same table and same database.
My DB name is BHOLENATH
my table name is ITEM
My ONE column name is DESCRIPTION (I want to copy all value from this column to EXTENDEDDESCRIPTION)
My second column name is EXTENDEDDESCRIPTION (this column currently has some value and I want to keep that value and more add from DESCRIPTION column.
Thank you Mr. Jones for your help.

USE BHOLENATH

UPDATE ITEM

SET EXTENDEDDESCRIPTION = CONVERT(NTEXT, NULLIF(LTRIM(RTRIM(ISNULL(CONVERT(NVARCHAR(MAX), EXTENDEDDESCRIPTION) + ' ', '') + ISNULL(DESCRIPTION, ''))), ''))

Make sure you understand what it is doing before using it.

--EDIT--

In your OP, you said that EXTENDEDDESCRIPTION was NTEXT. I've noticed in your second post you've said it is NVARCHAR(MAX), which post is correct? It changes the answer slightly because NTEXT is incompatible with + operations which is why I first had to convert it to NVARCHAR before converting the concatenated string back to NTEXT.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

grahamrd Valued Member Points: 54 More actions · Answer 4

Thank you Cadavre,

Your query is work very good and it's did what i want thank you very much:kiss:

I went to many forums spent about ten days but I can not find the right query but here in this forums I got very quick reply which does

work.

I really appreciate your on the time help . you are so gooood.

All I did is copy your query and paste in my query windows and it's work like a charm, great job. you are really helpful for my kind of NEWBIES who do not know even how to type a query, and you gave me exactly code which made my life lot easier, I am learning SQL

But your kind of people in the world who love to help newbies, which help us to learn fast, WE APPRECIATE VERY MUCH.

Thank You Very Much.

grahamrd.

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

grahamrd (9/26/2011)
Thank you Cadavre,
Your query is work very good and it's did what i want thank you very much:kiss:
I went to many forums spent about ten days but I can not find the right query but here in this forums I got very quick reply which does
work.
I really appreciate your on the time help . you are so gooood.
All I did is copy your query and paste in my query windows and it's work like a charm, great job. you are really helpful for my kind of NEWBIES who do not know even how to type a query, and you gave me exactly code which made my life lot easier, I am learning SQL
But your kind of people in the world who love to help newbies, which help us to learn fast, WE APPRECIATE VERY MUCH.
Thank You Very Much.
grahamrd.

No problems, glad it helped.

Did you understand what it was doing before you executed it?

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

grahamrd Valued Member Points: 54 More actions · Answer 6

Hi Cadavre,

Yes I understand and I was working on different computer then SQL server is installed means offline database.

But everything workout very good, ones again thanks to you.

grahamrd