| Author |
Topic |
|
jun0
Starting Member
32 Posts |
 Posted - 2012-12-14 : 09:28:22
|
| 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
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-14 : 09:42:51
|
| 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. |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-12-14 : 19:28:33
|
| 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! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-15 : 12:37:07
|
| 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-12-16 : 02:06:05
|
| 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 |
|
|
|
jun0
Starting Member
32 Posts |
Posted - 2012-12-16 : 07:33:35
|
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 |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-16 : 07:45:44
|
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. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-12-17 : 01:46:23
|
| 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 |
|
|
|
gtopawb
Starting Member
5 Posts |
Posted - 2012-12-27 : 02:15:14
|
| unspammed |
|
|
|
jun0
Starting Member
32 Posts |
Posted - 2013-01-03 : 09:31:44
|
| 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 :) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-03 : 11:06:50
|
| why do you need CAST in ALTER table?you just need thisALTER TABLE <Table Name> ALTER COLUMN <ColumnName> <NewDatatype> NULL/NOTNULL------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Change data type of populated column?