CODEWITHSUNDEEP
sqlsql-serverbulkinsert
Jatin
Jatin

Reputation: 31724

Bulk Insert - How to tell SQLServer to insert empty-string and not null

This seems like a trivial question. And it is. But I have googled for over a day now, and still no answer:

I wish to do a bulk insert where for a column whose datatype is varchar(100), I wish to insert an empty string. Not Null but empty. For example for the table:

create table temp(columnName varchar(100))

I wish to insert an empty string as the value:

BULK INSERT sandbox..temp FROM 
'file.txt' WITH ( FIELDTERMINATOR = '|#', ROWTERMINATOR   = '|:' );

And the file contents would be row1|:row2|:|:|:. So it contains 4 rows where last two rows are intended to be empty string. But they get inserted as NULL.

This question is not the same as the duplicate marked question: In a column, I wish to have the capacity to insert both: NULL and also empty-string. The answer's provided does only one of them but not both.

Upvotes: 1

Views: 2507

Answers (1)

Rahul
Rahul

Reputation: 77876

Well instead of inserting empty string explicitly like this why not let your table column have a default value of empty string and in your bulk insert don't pass any values for those columns. Something like

create table temp(columnName varchar(100) default '')

Upvotes: 3

Related Questions