Error converting data type varchar to numeric

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

General SQL Server Forums

New to SQL Server Programming

Error converting data type varchar to numeric

Author

Topic

maideen
Starting Member

10 Posts

Posted - 2014-07-06 : 01:08:20

Hi

i have issue in stored procedure converting data type

Destination table dbo.Z_tbl_CirculationCopy_TEMP has numeric data type
But source table Z_tbl_Import_temp have VarChar Type

If I export data from source, I have error message

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

Pls help to resolve this issue

Thanking you in advance

Maideen

Below is my store procedure


INSERT INTO dbo.Z_tbl_CirculationCopy_TEMP([MEMBER],[LANGUAGE],[TYPE],[PLATFORM],[CATEGORY],[MAINAREA],[STATE],[COPIES],
[MONTHNAME],[YEARNO],[REMARKS],[AVGISSUE_PAID],[AVGISSUE_FREE],[BCP_NTT],[BCP_50],[BCP_20],[BCP_20_Below],
[WP_TOTAL],[SABAH_TOTAL],[SARAWAK_TOTAL],[EM_Total])

SELECT [MEMBER],[LANGUAGE],[TYPE],[PLATFORM],[CATEGORY],[MAINAREA],[STATE],[COPIES],
[MONTHNAME],[YEARNO],[REMARKS],[AVGISSUE_PAID],[AVGISSUE_FREE],[BCP_NTT],[BCP_50],[BCP_20],[BCP_20_Below],
[WP_TOTAL],[SABAH_TOTAL],[SARAWAK_TOTAL],[EM_Total]

FROM (SELECT [MEMBER],[LANGUAGE],[TYPE],[PLATFORM],[CATEGORY],[MAINAREA],[MONTHNAME],[YEARNO],[REMARKS],
[AVGISSUE_PAID],[AVGISSUE_FREE],[BCP_NTT],[BCP_50],[BCP_20], [BCP_20_Below],
[WP_TOTAL],[SABAH_Total],[SARAWAK_Total],[EM_Total],
[Perlis],[Kedah],[Penang],[Perak],[Kelantan],[Terengganu],[Pahang],[Selangor],[WP],[NegriSembilan],[Melaka],[Johor],
[KotaKinaBalu],[sandakan],[Tawau],[Kudat],[Pendalaman],[Labuan],[SABAH_Other],
[Kuching],[Miri],[Sibu],[Bintulu],[SriAman],[KotaSemerahan],[Sarikei],[SARAWAK_Others],[Others]
FROM dbo.Z_tbl_Import_temp ) P

UNPIVOT (Copies FOR State IN ([Perlis],[Kedah],[Penang],[Perak],[Kelantan],[Terengganu],[Pahang],[Selangor],
[WP],[NegriSembilan],[Melaka],[Johor],
[KotaKinaBalu],[Sandakan],[Tawau],[Kudat],[Pendalaman],[Labuan],[SABAH_Other],
[Kuching],[Miri],[Sibu],[Bintulu],[SriAman],[KotaSemerahan],[Sarikei],[SARAWAK_Others],[Others]
))AS unpvt

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-07-06 : 05:48:28

Can you post some sample data that you're having trouble with?

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-07-07 : 01:37:58

An effective way to handle the process is to use CASE statement. Therefore whatever is evaluated - and if it doesn't fit the criteria of an INT , the code will not throw and error.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

yado
Starting Member

8 Posts

Posted - 2014-07-07 : 03:34:37

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources