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

Question

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.

PS: I am away of hacks like default constraint. Not interested in it. — Jatin
– Jatin, Commented Feb 16, 2017 at 13:02
Because in many cases, empty strings are also legitimate values. Long answer: In our large application, currently we do not use bulk and empty string were inserted. We plan to move to bulk. Cannot explicitly handle empty strings in the server code as it would unbelievably break a lot of things. — Jatin
– Jatin, Commented Feb 16, 2017 at 13:04
I think your title should include the fact that you're specifically inserting from a text file. I came here thinking, "oh this is trivial" because I thought it was a typical insert operation. You should also try to find more-specific tags. Is this feature (inserting from a text file like that) new to a particular version of SQL Server? I don't recall seeing it done that way. — rory.ap
– rory.ap, Commented Feb 16, 2017 at 13:08
@JoeC -- I'm not sold either. Null has meaning and empty string has meaning, and the two are separate. Not to mention the fact that if you compare two null values, it returns false. If you compare two empty strings, it returns true. — rory.ap
– rory.ap, Commented Feb 16, 2017 at 13:11

Rahul · Accepted Answer · 2017-02-16 13:09:30Z

3

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 '')

answered Feb 16, 2017 at 13:09

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rory.ap Over a year ago

The OP considers this approach a hack and said so in the comments.

rory.ap Over a year ago

I didn't DV, I still think this is a viable approach.

Rahul Over a year ago

@rory.ap, that's alright and didn't answered thinking about OP. It's a nice approach. If OP think it as hack then it's his personal opinion

Jatin Over a year ago

This does not answer the question. Reason: In the table, a column can have either a NULL or a legitimate value which is empty string. If I use, default '', then all the places where NULL was intended to be inserted, would be replaced by an empty string, which was not the intention. Now one could argue on why empty string and not null, that as a developer I have no control because that data (empty-string) means something to someone else.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related