Member Avatar for Maideen

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
[/code]
  • 2 Contributors
  • 1 Reply
  • 1K Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by hericles
Member Avatar for hericles

Try using the CAST or CONVERT functions of sql:

CAST(column_name AS INT)

Of course, your VarChar column needs to be all numbers for it to work.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.