SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Change data type of populated column?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jun0
Starting Member

United Kingdom
32 Posts

Posted - 12/14/2012 :  09:28:22  Show Profile  Reply with Quote
Firstly my apologies, as I also posted this in the SQL Server 2008 admin section, but I think it probably belongs here as hopefully its not too complex a thing to want to do:

is it possible to change the data type of a column of a table which contains data (quite a lot of data).

If this is possible, what is the best way to do it. Its pretty urgent and I'm looking for the quickest and simplest way.

Thank you!

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/14/2012 :  09:42:51  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Depends on how much data and whether you can afford downtime.

Copy into another table then rename is probably best?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1693 Posts

Posted - 12/14/2012 :  19:28:33  Show Profile  Reply with Quote
What are the original and desired datatypes? Will EVERY row of the existing data be legal in the new datatype? If the new datatype is smaller than the original, can you tolerate truncation?

=================================================
Hear the sledges with the bells - silver bells!
What a world of merriment their melody foretells!

Edited by - Bustaz Kool on 12/14/2012 19:31:49
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/15/2012 :  12:37:07  Show Profile  Reply with Quote
also need to check validity of data against new datatype and see if there are any values which cant be converted to new datatype

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2062 Posts

Posted - 12/16/2012 :  02:06:05  Show Profile  Visit jackv's Homepage  Reply with Quote
Create a new column with the new data type. Move data from the old column into the new column.Once you've reconciled the columns - checked for conversion errors etc, rename the old one , name the new oneto the originl name.


Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Edited by - jackv on 12/17/2012 01:33:00
Go to Top of Page

jun0
Starting Member

United Kingdom
32 Posts

Posted - 12/16/2012 :  07:33:35  Show Profile  Reply with Quote
if I add a new column to the table with the desired datatype and move the data to that, then drop the original column, I think this might do it thanks.

I will try this, but how do you move an entire columns data to another column and make sure that the data goes into the same rows it was before? I guess this will just happen anyway and isn't anything to worry about... but just wanted to make sure

Whats the SQL to move all of the data in one column to a new column, the table is millions of rows in size, will this take a long time to complete?

Thanks
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/16/2012 :  07:45:44  Show Profile  Reply with Quote
The code for what Jack suggested is as follows:
ALTER TABLE YourTableName ADD TheNewColumn INT;

UPDATE YourTableName SET TheNewColumn = CAST(YourVarcharColumn AS INT);
Run the two statements separately. Also, test in a dev environment.

Couple of things I want to mention about the approach Jack suggested is that the ordinal position of the column would change when you are done. Good programmers should not rely on the ordinal position of the columns in their select statements or other client applications, but not everyone is a good programmer (present company excepted, of course).

The other thing you may want to look up is whether your existing column is a foreign key. If it is, again you need to make similar changes there as well. That needs to be carefully planned and carried out, if that is required.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2062 Posts

Posted - 12/17/2012 :  01:46:23  Show Profile  Visit jackv's Homepage  Reply with Quote
Agreed with comment on ordinal position. Normally , you'd do something like : SELECT col1, col2, which gets around any problems for ordinal position.


Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

gtopawb
Starting Member

5 Posts

Posted - 12/27/2012 :  02:15:14  Show Profile  Reply with Quote
unspammed
Go to Top of Page

jun0
Starting Member

United Kingdom
32 Posts

Posted - 01/03/2013 :  09:31:44  Show Profile  Reply with Quote
How can I change the data type of the column using the alter table statement, without using the CAST? as apparently this will take too no long/cause too much lag considering I have millions of rows in my table.

Is there another way to do this, relatively simple, using the alter table statement, or indeed, any statment?

What would be the best way, I have got to the point where there are no non numeric values in the column, I'm ready to convert the data type.

Thanks guys :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/03/2013 :  11:06:50  Show Profile  Reply with Quote
why do you need CAST in ALTER table?

you just need this

ALTER TABLE <Table Name> ALTER COLUMN <ColumnName> <NewDatatype> NULL/NOTNULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000