• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 590
  • Last Modified:

ALTER Table command to change datatype and default value

Hi all -
I have a table X with a column Y of datatype varchar and a default value of '1'.
The column contains existing numerical data but of type varchar.
How do I alter the column to change datatype to INT and update the default value to 1 (instead of '1')  ?
How do I change the existing data in the column to INT ?
0
deirekn
Asked:
deirekn
1 Solution
 
cyberkiwiCommented:
declare @sql nvarchar(max)
select @sql='alter table X drop constraint ' + object_name(constid) from sysconstraints where id=object_id('X')
exec (@sql);
alter table X alter column Y int;
alter table X add constraint df_X_Y default(1) for Y;
0
 
deireknAuthor Commented:
Thank you. That worked great.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now