[FIXED] How to insert empty string (' ') to decimal or numeric in SQL Server?

Issue

Is there a way to insert an empty string (' ') into a column of type decimal?

This is for the example :

create table #temp_table 
(
    id varchar(8) NULL,
    model varchar(20) NULL,
    Plandate date NULL,
    plan_qty decimal (18, 0) NULL,
    Remark varchar (50) NULL,
)

insert into #temp_table (id, model, Plandate, plan_qty, Remark)
values ('pn-01', 'model-01', '2017-04-01', '', 'Fresh And Manual')

I get an error

Error converting data type varchar to numeric.

My data is from an Excel file and has some blank cells.

My query read that as an empty string.

If there is a way, please help.

Thanks

Solution

Based off your comments you just need a case statement to your insert from excel, which I assume is using open query or bulk insert.

Insert into #temp_table (id, model, Plandate, plan_qty, Remark)
Select
...
Case when someColumn = '' then null else someColumn end
...
From
--your excel file via openquery or whatever ...

Answered By - S3S

Answer Checked By - Willingham (PHPFixing Volunteer)