|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
converting a column from varchar to intI need to copy the data from varchar column into an integer column,
obviously only copying integer values. How can I do this? I am not even sure how to select data from a column so that the results are only the numeric ones. Thanks for your help. Et,
ALTER TABLE should allow you to change the datatype of the table assuming all values are integers. You could run something like this: UPDATE tablename SET col2 = CAST(col1 AS int) --would require the values to be integers not characters Check out the ISNUMERIC function for differentiating between integers and characters. Also see: http://www.aspfaq.com/show.asp?id=2390 HTH Jerry Show quote "et" <eagletender2***@yahoo.com> wrote in message news:%23nc3kkdxFHA.1252@TK2MSFTNGP09.phx.gbl... >I need to copy the data from varchar column into an integer column, >obviously only copying integer values. How can I do this? I am not even >sure how to select data from a column so that the results are only the >numeric ones. Thanks for your help. > Yes, the isnumeric function worked, the cast of course gave me an error
because not all values are integers. This is what I did: update tbl set newcol=originalcol where isnumeric(originalcol)=1 Now I have to populate the remaining fields with an integer, any idea an easy way to do that? I'd like to do it starting with the next available number, but it doesn't really matter what the number ends up being. This column will eventually be an identity column. Thanks! Show quote "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message news:eMYJevdxFHA.2232@TK2MSFTNGP11.phx.gbl... > Et, > > ALTER TABLE should allow you to change the datatype of the table assuming > all values are integers. > > You could run something like this: > > UPDATE tablename > SET col2 = CAST(col1 AS int) --would require the values to be integers > not characters > > Check out the ISNUMERIC function for differentiating between integers and > characters. Also see: > > http://www.aspfaq.com/show.asp?id=2390 > > HTH > > Jerry > > > "et" <eagletender2***@yahoo.com> wrote in message > news:%23nc3kkdxFHA.1252@TK2MSFTNGP09.phx.gbl... >>I need to copy the data from varchar column into an integer column, >>obviously only copying integer values. How can I do this? I am not even >>sure how to select data from a column so that the results are only the >>numeric ones. Thanks for your help. >> > > ok...clarify please...IDENTITY is system generated so you don't populate it
with any values. "any number"? Show quote "et" <eagletender2***@yahoo.com> wrote in message news:O3CsMRhxFHA.464@TK2MSFTNGP15.phx.gbl... > Yes, the isnumeric function worked, the cast of course gave me an error > because not all values are integers. This is what I did: > > update tbl set newcol=originalcol > where isnumeric(originalcol)=1 > > Now I have to populate the remaining fields with an integer, any idea an > easy way to do that? I'd like to do it starting with the next available > number, but it doesn't really matter what the number ends up being. This > column will eventually be an identity column. > > Thanks! > > "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message > news:eMYJevdxFHA.2232@TK2MSFTNGP11.phx.gbl... >> Et, >> >> ALTER TABLE should allow you to change the datatype of the table assuming >> all values are integers. >> >> You could run something like this: >> >> UPDATE tablename >> SET col2 = CAST(col1 AS int) --would require the values to be integers >> not characters >> >> Check out the ISNUMERIC function for differentiating between integers and >> characters. Also see: >> >> http://www.aspfaq.com/show.asp?id=2390 >> >> HTH >> >> Jerry >> >> >> "et" <eagletender2***@yahoo.com> wrote in message >> news:%23nc3kkdxFHA.1252@TK2MSFTNGP09.phx.gbl... >>>I need to copy the data from varchar column into an integer column, >>>obviously only copying integer values. How can I do this? I am not even >>>sure how to select data from a column so that the results are only the >>>numeric ones. Thanks for your help. >>> >> >> > > That's correct. In other words if this database had been done correctly in
the first place, it would have been an identity column. So now I need to create this identity column based on numbers that already exist, plus populate those records with new numbers for those records that don't use an integer value. Once this is done, then I can make the column an identity column so that future new records are automatically numbered correctly. Does that make sense? Show quote "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message news:eBZ%23xThxFHA.1412@TK2MSFTNGP09.phx.gbl... > ok...clarify please...IDENTITY is system generated so you don't populate > it with any values. "any number"? > > "et" <eagletender2***@yahoo.com> wrote in message > news:O3CsMRhxFHA.464@TK2MSFTNGP15.phx.gbl... >> Yes, the isnumeric function worked, the cast of course gave me an error >> because not all values are integers. This is what I did: >> >> update tbl set newcol=originalcol >> where isnumeric(originalcol)=1 >> >> Now I have to populate the remaining fields with an integer, any idea an >> easy way to do that? I'd like to do it starting with the next available >> number, but it doesn't really matter what the number ends up being. This >> column will eventually be an identity column. >> >> Thanks! >> >> "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message >> news:eMYJevdxFHA.2232@TK2MSFTNGP11.phx.gbl... >>> Et, >>> >>> ALTER TABLE should allow you to change the datatype of the table >>> assuming all values are integers. >>> >>> You could run something like this: >>> >>> UPDATE tablename >>> SET col2 = CAST(col1 AS int) --would require the values to be integers >>> not characters >>> >>> Check out the ISNUMERIC function for differentiating between integers >>> and characters. Also see: >>> >>> http://www.aspfaq.com/show.asp?id=2390 >>> >>> HTH >>> >>> Jerry >>> >>> >>> "et" <eagletender2***@yahoo.com> wrote in message >>> news:%23nc3kkdxFHA.1252@TK2MSFTNGP09.phx.gbl... >>>>I need to copy the data from varchar column into an integer column, >>>>obviously only copying integer values. How can I do this? I am not >>>>even sure how to select data from a column so that the results are only >>>>the numeric ones. Thanks for your help. >>>> >>> >>> >> >> > > You cannot alter an existing column to be an identity column. You'll have to
create a new table with the identity column and then copy the rows from the old table into the new one using SET IDENTITY_INSERT ON. Why are you doing this? Are you planning on using the identity column as the primary key? Analyze your data more thoroughly - there may be a better primary key candidate. To clean up those values try this: http://milambda.blogspot.com/2005/03/fail-safe-conversion-to-integer.html ML No, my primary key is a guid. This column used to be the primary key in an
access database, and was also used for other reasons. Now it's being merged with the company's sql database. I'm only using the identity feature so it will automatically provide a number each time a new record is created, which of course I can do within the program it's being used with also. Show quote "ML" <M*@discussions.microsoft.com> wrote in message news:53200E31-72FB-4C15-AF6B-CF6858E3296E@microsoft.com... > You cannot alter an existing column to be an identity column. You'll have > to > create a new table with the identity column and then copy the rows from > the > old table into the new one using SET IDENTITY_INSERT ON. > > Why are you doing this? Are you planning on using the identity column as > the > primary key? Analyze your data more thoroughly - there may be a better > primary key candidate. > > To clean up those values try this: > http://milambda.blogspot.com/2005/03/fail-safe-conversion-to-integer.html > > > ML If the values still need to be unique, then I'd suggest letting the server
assign them. ML  |
|||||||||||||||||||||||
Other interesting topics