Solved

ALTER Table command to change datatype and default value

Posted on 2010-09-20
2
578 Views
Last Modified: 2012-08-13
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
Comment
Question by:deirekn
2 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33715271
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
 

Author Closing Comment

by:deirekn
ID: 33727246
Thank you. That worked great.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now