March 25, 2014 at 4:30 am
Hi,
I've imported text files into SQL server and by default the fields are varchar(50) data type.
I'm trying to alter certain columns but I keep getting an error. Any advise?
"Error converting data type varchar to real...."
ALter table [dbo].[Voice_PAYG] alter column [MO_BILLED_MINUTES] real;
ALter table [dbo].[Voice_PAYG] alter column [MO_ACTUAL_MINUTES] dec(18,2);
ALter table [dbo].[Voice_PAYG] alter column [CALLS] dec(18,2);
ALter table [dbo].[Voice_PAYG] alter column [UNIQUE_CALLERS] dec(18,2);
ALter table [dbo].[Voice_PAYG] alter column [MO_NET_REVENUE] dec(18,2);
ALter table [dbo].[Voice_PAYG] alter column [MO_REVENUE] real;
Is there a script that can convert columns of existing tables?
Thanks!
March 25, 2014 at 5:55 am
I'd suggest double checking the data in the column. It sounds like you may have text data mixed in with the numbers.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 25, 2014 at 6:41 am
If there are several columns that need changed you may be better off creating a new table and inserting the data there.
As Grant said you need to check your data, which could be done with the select statement used to insert the data.
--INSERT INTO abc (X)
SELECT CAST( X AS REAL) AS X
If you do one column at a time you will find the problem.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply