import in query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

import in query

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-05-13 : 12:56:10

I have created a table successfully with the following statement:

create table State_Budget_Shortfall_09 (state_id varchar (4), state_name varchar (50), shortfall varchar (250), short_before_bud_adopted varchar (250), shortfall_millions varchar (255), addl_funds_needed_pct_GF decimal (3,2), bud_gap_millions money, gap_pct_GF decimal (3,2))

But when I try to import using this statement:

insert into State_Budget_Shortfall_09 (state_id , state_name, shortfall, short_before_bud_adopted, shortfall_millions, addl_funds_needed_pct_GF, bud_gap_millions, gap_pct_GF) values ('2', 'Alabama', 'Yes', '$784', '$1,100', '12.7%', '$1,800', '22.2%');

Here is the error message:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Can anyone help me? What am I doing wrong? Is there a way around this?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 13:00:26

dont pass %,$,... caharcaters to decimal fields


insert into State_Budget_Shortfall_09 (state_id , state_name, shortfall, short_before_bud_adopted, shortfall_millions, addl_funds_needed_pct_GF, bud_gap_millions, gap_pct_GF) values ('2', 'Alabama', 'Yes', '$784', '$1,100', 12.7, 1800, 22.2);

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-05-13 : 13:17:47

quote:

Originally posted by visakh16

dont pass %,$,... caharcaters to decimal fields


insert into State_Budget_Shortfall_09 (state_id , state_name, shortfall, short_before_bud_adopted, shortfall_millions, addl_funds_needed_pct_GF, bud_gap_millions, gap_pct_GF) values ('2', 'Alabama', 'Yes', '$784', '$1,100', 12.7, 1800, 22.2);

Thanks, I will try getting rid of them now.

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-05-13 : 13:21:40

I took all of the $, % characters out and still get an error:

Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting varchar to data type numeric.
The statement has been terminated.

(1 row(s) affected)
Msg 8115, Level 16, State 8, Line 3
Arithmetic overflow error converting varchar to data type numeric.
The statement has been terminated.
Msg 8114, Level 16, State 5, Line 4
Error converting data type varchar to numeric.

Do you know what this means?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 13:26:46

yup. this means you're trying to put in a numeric field a varchar value which when converted happens to be outside its range.
are you sure you've only single insert statement you posted?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources