CODEWITHSUNDEEP
sqlsql-servert-sqlerror-handlingnull
wel
wel

Reputation: 244

What is the right way to handle type string null values in SQL's Bulk Insert?

For example, I have a column with type int.

The raw data source has integer values, but the null values, instead of being empty (''), is 'NIL'

How would I handle those values when trying to Bulk Insert into MSSQL?

My code is

create table test (nid INT);

bulk insert test from @FILEPATH with (format="CSV", firstrow=2);

the first 5 rows of my .csv file looks like

1
2
3
NIL
7

Upvotes: 0

Views: 190

Answers (1)

gotqn
gotqn

Reputation: 43666

You can replace the nil with " (empty string) directly in your data source file or insert the data into a staging table and transform it:

BULK INSERT staging_sample_data
FROM '\\data\sample_data.dat';

INSERT INTO [sample_data] 
SELECT NULLIF(ColA, 'nil'), NULLIF(ColB, 'nil'),...

Of course if your field is for example a numeric, the staging table should have a string field. Then, you can do as Larnu offers: 'TRY_CONVERT(INT, ColA)'.

*Note: if there are default constraints you may need to check how to keep nulls

Upvotes: 1

Related Questions