-1

I want to load a data file into the SQL Server database using the BCP command.

The Data file has some data as NULL(string) or NA but the columns type in the database is numeric. e.g

ABC|TEST DRUG 10A|ADBC|true|200.0000|EA|false|2020-10-24|true|NP|0|NA|NA|NA|NA|NA|NA|B|NA

I want to handle it during loading such that data going inside the database is ''.

I tried the normal as well native approach, but it's failing.

I have two alternate in my mind but still another way round

  1. Change destination data type from numeric to varchar for handling null values.
  2. Kindly check with the data source and get 'NA' & 'NULL' keywords replaced with blank spaces.

Can someone suggest me a better approach Thanks in advance

Dale K
  • 25,246
  • 15
  • 42
  • 71
Amarnath Pandey
  • 1

1 Answers1

0

Consider using a staging table to import the data to using BCP. This is an intermediate table, from which you would convert to a final destination in a second step (not using BCP).

The staging table would have varchar columns for the columns that can have NA values instead of numeric values.

A second step would then convert the NA values to NULL using a CASE statement for instance:

CASE WHEN col_num_or_na='NA' 
    THEN NULL
    ELSE TRY_CAST(col_num_or_na AS NUMERIC(19,6))
END
TT.
  • 15,774
  • 6
  • 47
  • 88