Search code examples
sqlsql-serverstringdecimalnumeric

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

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 expression 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 ...