change data type

Question

Post reply

change data type

nazaninahmady_sh

Default port

Points: 1465
More actions
December 3, 2008 at 3:34 am

#128328

hello.,
i want to change type of data from (ntext) to (nvarchar(max)), is there any way?and is yes, how?
regards

Viewing 15 posts - 1 through 15 (of 16 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

steveb.

SSC-Forever

Points: 46733

December 3, 2008 at 4:10 am

#906724

there is a way;

CAST(YourTextCol AS NVARCHAR(MAX))

CrazyMan SSChampion Points: 12560 More actions · Answer 2

if this a adhoc Query ones then cast as said above will do , if you want to change the structure of the table, yes you can

nazaninahmady_sh Default port Points: 1465 More actions · Answer 3

thank you for your reply

i want to get data with (ntext) type from one Database and input them in a another Database with (navarchar) type and for is purpose i have a i have a Sub Query and an Insert in it, i dont know what i should do in his Subquery?

thanks

steveb. SSC-Forever Points: 46733 More actions · Answer 4

nazaninahmady_sh (12/3/2008)
thank you for your reply
i want to get data with (ntext) type from one Database and input them in a another Database with (navarchar) type and for is purpose i have a i have a Sub Query and an Insert in it, i dont know what i should do in his Subquery?
thanks

All you need to do is modify the code to include the cast code that I posted earlier for the ntext col.

Post your insert code if you need more specific help

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Can you post the schema of the two tables and the query please?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nazaninahmady_sh Default port Points: 1465 More actions · Answer 6

thank you very much for your reply

the Schema of source table is :

id int

title nvarchar (250)

body ntext

i want to write a query for transfering data from Source table to destination table and in future this will be a step of a job.but there is a difference with these two , in my destination table i dont want to have a field with ntext type i want to to change my boby data type to nvarcharmax.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

What does the schema of the destination table look like?

You said you had a query to copy the data over. Can you post it?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nazaninahmady_sh Default port Points: 1465 More actions · Answer 8

the Schema of Destination table is :

id int

title nvarchar (250)

body navarchar(max)

A you see the only difference between Source table and Destination table is in type of body (field), body in Source has ntext type, and in Destination has nvarchar(max) type.

about the Query i am trying to write it but i can not,

because the ntext is pointer and it save data in different places i can not write a query for transfering data of body ntext from source table to body nvarchar in Destination table.

again thanks alot

steveb. SSC-Forever Points: 46733 More actions · Answer 9

you can use

INSERT INTO [Destination] (id ,title ,body)

SELECT id ,title ,cast(body as varchar(max)) from [Source]

Though I think that the data conversion may not be needed as it should do this automaticaly, I will have to check this

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 10

nazaninahmady_sh (12/3/2008)
because the ntext is pointer and it save data in different places i can not write a query for transfering data of body ntext from source table to body nvarchar in Destination table.

You shouldn't need to do a conversion. SQL should be able to implicitly convert the ntext to nvarchar(max). Are you getting an error if you try to insert without an explicit conversion?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nazaninahmady_sh Default port Points: 1465 More actions · Answer 11

sorry

if i want to write this Query as Trigger for examinaton , what should i do?

steveb. SSC-Forever Points: 46733 More actions · Answer 12

steveb.

SSC-Forever

Points: 46733

December 3, 2008 at 9:21 am

#906970

What sort of trigger?

nazaninahmady_sh Default port Points: 1465 More actions · Answer 13

yes , i have problem when i want to do this conversin through SQL, because ntext is pointer az i told you it save my data in different places not in one place, and when i want to transfer all of data to another field with another data type in another table in another database i have problem because , i can not transfer all of myy data (wich is save in differet places)

i have heard the only thing that can help is (text reader) or (pointer reader) i dont know its name exactly.

nazaninahmady_sh Default port Points: 1465 More actions · Answer 14

but for examination your previouse help i want to write an after Trigger ,please help me.

thanks