CODEWITHSUNDEEP
sql-server
Samiksha
Samiksha

Reputation: 6192

How to change the data type of a column without dropping the column with query?

I have a column which has a datatype : datetime. But now i want to convert it to datatype varchar. Can i alter the datatype without droppping the column? If yes, then please explain how?

Upvotes: 62

Views: 370352

Answers (15)

SiwachGaurav
SiwachGaurav

Reputation: 1966

Type the below query:

alter table table_Name alter column column_name datatype

e.g.

alter table Message alter column message nvarchar(1024);

Upvotes: 10

Mark Brittingham
Mark Brittingham

Reputation: 28875

If ALTER COLUMN doesn't work.

It is not unusual for alter column to fail because it cannot make the transformation you desire. In this case, the solution is to create a dummy table TableName_tmp, copy the data over with your specialized transformation in the bulk Insert command, drop the original table, and rename the tmp table to the original table's name. You'll have to drop and recreate the Foreign key constraints and, for performance, you'll probably want to create keys after filling the tmp table.

Sound like a lot of work? Actually, it isn't.

If you are using SQL Server, you can make the SQL Server Management Studio do the work for you!

  • Bring up your table structure (right-click on the table column and select "Modify")
  • Make all of your changes (if the column transformation is illegal, just add your new column - you'll patch it up in a moment).
  • Right-click on the background of the Modify window and select "Generate Change Script." In the window that appears, you can copy the change script to the clipboard.
  • Cancel the Modify (you'll want to test your script, after all) and then paste the script into a new query window.
  • Modify as necessary (e.g. add your transformation while removing the field from the tmp table declaration) and you now have the script necessary to make your transformation.

Upvotes: 33

Mika
Mika

Reputation: 49

ALTER TABLE yourtable MODIFY COLUMN yourcolumn datatype

Upvotes: 0

faisal khan
faisal khan

Reputation: 21

ALTER TABLE table_name
MODIFY (column_name data_type);

Upvotes: 0

Vinda
Vinda

Reputation: 223

ORACLE - Alter table table_name modify(column_name new_DataType);

Upvotes: 0

Pratyay
Pratyay

Reputation: 2316

ALTER TABLE [table name] MODIFY COLUMN [column name] datatype

Upvotes: 14

atrichkov
atrichkov

Reputation: 477

This work for postgresql 9.0.3

 alter table [table name] ALTER COLUMN [column name] TYPE [character varying];

http://www.postgresql.org/docs/8.0/static/sql-altertable.html

Upvotes: 3

ATUL KARMAKAR
ATUL KARMAKAR

Reputation: 1

alter table [table name] remove [present column name] to [new column name.

Upvotes: -4

user2650031
user2650031

Reputation: 11

ALTER TABLE YourTableNameHere ALTER COLUMN YourColumnNameHere VARCHAR(20) this is perfect for change to datatype

Upvotes: 1

Jesse Pepper
Jesse Pepper

Reputation: 3243

ALTER tablename MODIFY columnName newColumnType

I'm not sure how it will handle the change from datetime to varchar though, so you may need to rename the column, add a new one with the old name and the correct data type (varchar) and then write an update query to populate the new column from the old.

http://www.1keydata.com/sql/sql-alter-table.html

Upvotes: -2

capotej
capotej

Reputation: 2971

ALTER TABLE YourTableNameHere ALTER COLUMN YourColumnNameHere VARCHAR(20)

Upvotes: 10

Leon Tayson
Leon Tayson

Reputation: 5011

ALTER TABLE YourTableNameHere ALTER COLUMN YourColumnNameHere VARCHAR(20)

Upvotes: 7

duc14s
duc14s

Reputation: 181

With SQL server 2008 and more, using this query:

ALTER TABLE [RecipeInventorys] ALTER COLUMN [RecipeName] varchar(550)

Upvotes: 6

Syed Baqar Hassan
Syed Baqar Hassan

Reputation: 2643

ALTER TABLE [table_name] ALTER COLUMN [column_name] varchar(150)

Upvotes: 1

devio
devio

Reputation: 37225

MSDN says

ALTER TABLE mytable ALTER COLUMN mycolumn newtype

Beware of the limitations of the ALTER COLUMN clause listed in the article

Upvotes: 95

Related Questions