change the datatype of a coumn

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I have a table with some data already in it in SQLSERVER 2005.

I want to change the data type of a column.
When I tried to change the type of the column it is saying a message like "You can't change this because you already have data in this table so you need to drop and recreate the table".

Any solution for this to change the data type of the column in a table even it has some data in it?

I tried to take the backup of the single table, so that I will drop and create the new table and then post this backup table data in it but I didn't find an option to take the backup of single table.

Is there any way to take a backup of single table?

Posted 9-Aug-10 0:21am

yadlaprasad

Updated 9-Aug-10 0:42am

Dalek Dave

v2

Add a Solution

Comments

Dalek Dave 9-Aug-10 6:42am

Edited for Readability, Spelling, Grammar and Syntax.

1 solution

Add a Solution

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

E.F. Nijboer · Answer 1 · 2010-08-09T00:27:00

You create a new table and copy the data into it (even convert the column data already). Then you can clear it and change the column and copy it back. Most of the time you could also just drop the table and rename the new table.

By the way, are you using SQL to perform this or using the server manager menu's?

You could try SQL directly and see if this works, like:

SQL

ALTER TABLE [dbo].[person]
ALTER COLUMN [lastname] VARCHAR(35) NULL
GO

Won't work though if this is (part of) the primary key.

Good luck!

change the datatype of a coumn

1 solution

Solution 1

Add your solution here

Preview 0

change the datatype of a coumn

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us