Change column datatype in Sql server table

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.