How to change column datatype in SQL Server database without losing data

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

I have SQL Server database and I just realized that I can change the type from one of the columns from text to ntext. How can I do that without losing the data that is already entered into that table?

PS. I am using SQL Server Server 2008.

Posted 23-Sep-14 22:14pm

zainab77

Add a Solution

Comments

Gihan Liyanage 24-Sep-14 4:33am

If you are using text, then you can simply change it to ntext.. No data will be change

zainab77 24-Sep-14 6:30am

i did but sql didnt accept ??

Gihan Liyanage 24-Sep-14 6:42am

What you have done ? You used any command or did using Management Studio? What you get as the error ?

Gihan Liyanage 24-Sep-14 6:51am

See this article
http://support.microsoft.com/kb/196935

RossMW 24-Sep-14 5:46am

As a side comment. If you are changing the type that will lose the data you can add a new field with the new data type and convert and copy the data to it, then delete the old and rename the new field.

KALYANI BIBIN 24-Sep-14 6:37am

1. First u copy the records to temp table
select * into temp from table1 (it will automatically create a table named temp)

2. drop table table1
3.
create table table1(id int,name ntext(25))
insert into table1(name) select name from temp

King Fisher 24-Sep-14 7:08am

please Refer my solution if your Doing the same

Richard Deeming 24-Sep-14 8:08am

Since you're using SQL 2005 or higher, you should avoid the text and ntext types. Use varchar(max) and nvarchar(max) instead.

Similarly, avoid the image type; replace it with varbinary(max).

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server.
http://msdn.microsoft.com/en-GB/library/ms187993.aspx[^]

NTEXT vs NVARCHAR(MAX) in SQL 2005[^]

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Answer 1 · 2014-09-24T00:56:00

Solution 2

Check this link for couple of advices.

SQL Server 2000 - How do I alter a column from text to ntext?[^]

Posted 24-Sep-14 0:56am

_Asif_

King Fisher · Answer 2 · 2014-09-24T01:05:00

Steps:
Sql server - Tools -> Options ->Designers ->

There You can find the Auto Generate Change Scripts which is unchecked,so make it as Check.

Uncheck the 
prevent saving Changes that requires Table re-Creation 

Save ->

Now open your table design and Change your datatype as text to ntext and Save it.

it will ask for Overwrite then go with Yes.

Nilesh_AES · Answer 3 · 2014-09-24T01:06:00

Solution 4

Alter Table TableName
Alter Column ColumnName YourDataType

Posted 24-Sep-14 1:06am

Nilesh_AES