Change column datatype in Sql server table

Posted on 2013-10-13
Medium Priority
Last Modified: 2013-10-14
Hi Expert,

I have to change a column 'Product Code' datatype from integer to Varchar and  have several tables use it as foreign key, how i can change it and not lost any data?

Thanks in advance.
Question by:dshi15
  • 3
LVL 11

Assisted Solution

HuaMinChen earned 400 total points
ID: 39569276
1. Use a temp table for doing this
2. Use
for converting the column from Integer to varchar, like
cast(123 as varchar)
3. Reference this to create FK
Create Foreign Key
LVL 66

Accepted Solution

Jim Horn earned 1600 total points
ID: 39569313
Just a thought, why are you using a varchar as a foreign key, if the values in it are numeric?
Numeric values usually take up less bytes in memory, making for a more efficient key.

Either way, the T-SQL will go something like this.  Assuming 10 characters is enough, rename the obvious, make a backup of your table first, and execute.
-- Add an extra column
ALTER TABLE your_table
ADD new_product_code varchar(10)  

-- Move the values
UPDATE your_table
SET new_product_code = CAST(product_code as varchar(10))

-- Drop the old column, 
ALTER TABLE your_table
DROP COLUMN product_code

-- Rename new to old
exec sp_rename 'your_table.new_product_code', 'product_code', 'COLUMN'

-- Then create FK's like this for all tables
ALTER TABLE your_table
ADD CONSTRAINT fk_name_me FOREIGN KEY (product_code)
REFERENCES some_other_table (that_table_product_code_column_name)

Open in new window

LVL 66

Expert Comment

by:Jim Horn
ID: 39569318
IF you are confident about all the current values and don't need to change any first, you could just change the data type, skipping all of the add-update-drop-rename, then create your FK's.
ALTER TABLE your_table
ALTER COLUMN product_code varchar(10)

Open in new window

LVL 66

Expert Comment

by:Jim Horn
ID: 39570611
dshi15 - Since this question is closed, you can open up another question, spell out your requirements for wanting to do this, and we can give you some design advice.  

Using a varchar as a key is akin to playing with fire..

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question