| Author |
Topic |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2012-02-27 : 10:39:34
|
| Hopefully that's all this is. I have a table in my db with 1.5M+records. Some of the columns are INT and i need to change them to varchar's. When i attempt to do so, likely because there are so many records that need to be upated, i get a timeout error:'mnione' table- Unable to modify table. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.I'm changing that field to a varchar length that is longer than any of the current INT records, so i know (or think) i'm not hitting a conflict based on that. Where do i change the timeout period in mgmt studio for queries such as this? (if in fact that's my solution).thanks |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-02-27 : 10:41:26
|
| Disregard. Rather than changing the existing field to a varchar, i'm adding a paralell field and doing an update for the new field based on the existing INT field (which is what i should have done all along). |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-02-27 : 11:03:52
|
| I'm converting a client's data over to our system. in their table, eyecolor is coded in the EYECOLORID field as an INT (i.e. 1115=Blue eyes, 64=brown eyes, etc.). in our system, eye color is in the EYES column and is a varchar(3) with a value of BRO, BLU, GRN, etc. I just created the EYES column and update EYES='BLU' where EYECOLORID='1115' etc, etc, and so on.Mind-numbingly boring stuff. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-27 : 11:16:57
|
| Don't use the GUI 'wizards' to modify tables, they do odd things and have very low timeouts.Query window and specify the ALTER TABLE statement that you need.--Gail ShawSQL Server MVP |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-02-27 : 11:30:57
|
| duly noted. thank you. |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-02-27 : 11:39:02
|
| one of the columns in this table for which the old name is the same as the new name is "State". The customer's old data is still an INT 'value', even though the column is actually a varchar(5), representing each state, but our value is the two character abbreviation that the 'real world' uses to identify our states. So, as i find the value in their CombinedDesc table which shows how their numeric values represent states, i have to go through and update the value for state. i then do another select to find the remaining numerical values in that column and weed out the values that i've already converted (Select * from MNIONE where state<>'FL' and state <>'GA' and state <>'AL') It is getting tedious to keep adding <>'s to that line. Is there a way to use a SELECT which will show me values that are not alphabetic? I'm guessing at this point, Select * from MNIONE where State is not like '[%ABC%]'. If that's the case does the value between the %'s need to be all of A-Z? or am i just being lazy? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-27 : 12:15:39
|
| You want just the numeric values?WHERE ISNumeric(State) = 1orWHERE State NOT LIKE '%[^0-9]%' (I think, guessing a little here, I never can get the numerics right)--Gail ShawSQL Server MVP |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-02-27 : 12:21:20
|
| Perfect. Thank you. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-02-27 : 14:14:02
|
| What is the advantage to using that vs.:--UPDATE MNIone SET STATE = 'NM' WHERE STATE='1096'--UPDATE MNIone SET STATE = 'NV' WHERE STATE='1097'--UPDATE MNIone SET STATE = 'OR' WHERE STATE='1101'--UPDATE MNIone SET STATE = 'SD' WHERE STATE='1105'--UPDATE MNIone SET STATE = 'VT' WHERE STATE='1110'--UPDATE MNIone SET STATE = 'WY' WHERE STATE='1114'--NAME STRUCTUREUPDATE MNIONE SET LNAME =SUBSTRING(LASTNAME,1,20)UPDATE MNIONE SET MNAME=SUBSTRING(MIDDLENAME,1,15)UPDATE MNIONE SET FNAME=SUBSTRING(FIRSTNAME,1,15)just curious.thanks |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Configuration tweak