1

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.

Jatin
  • 31,116
  • 15
  • 98
  • 163
  • PS: I am away of hacks like default constraint. Not interested in it. – Jatin Feb 16 '17 at 13:02
  • Empty strings... why? – jarlh Feb 16 '17 at 13:03
  • 1
    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 Feb 16 '17 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 Feb 16 '17 at 13:08
  • Nulls are your friend, you just need to get to know them a little better :). I worked with people moving from older databases to SQL Server that preferred empty strings. By the time I walked through all their scenarios they thought would be a problem, they changed their minds and prefer nulls. If you want to something outside the accepted behavior of a tool like bulk insert then default constraints and not null field property are a good way to go. – Joe C Feb 16 '17 at 13:08
  • http://stackoverflow.com/questions/15581814/insert-a-empty-string-on-sql-server-with-bulk-insert – Joe C Feb 16 '17 at 13:09
  • 1
    @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 Feb 16 '17 at 13:11
  • Bulk insert into a staging table, fix any anomalies before updating your production tables. – Lasse V. Karlsen Feb 16 '17 at 13:12
  • @rory.ap On PostgreSQL this gives different results: `SELECT (NULL = NULL) = FALSE UNION ALL SELECT FALSE = FALSE` So basically, comparing `unknown` with `unknown` should give `unknown` as well, with does not evaluate for `true` with is often requirement for conditional expressions. But it doesn't return false, with could lead to some problems if you assume it does. – Łukasz Kamiński Feb 16 '17 at 13:16
  • @JoeC This question is not same as that one. In that case, he is happy to use a default value. In my case, I wish to insert both: null and empty string. So they are not the same – Jatin Feb 16 '17 at 16:28
  • That accepted answer states that you have no other option when using Bulk Insert. Here is another link https://msdn.microsoft.com/en-us/library/ms187887.aspx. And another https://msdn.microsoft.com/en-us/library/ms162802.aspx. If you read through them you will see that nulls or default values are the only options. – Joe C Feb 16 '17 at 16:59

1 Answers1

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 '')
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • 3
    The OP considers this approach a hack and said so in the comments. – rory.ap Feb 16 '17 at 13:12
  • I didn't DV, I still think this is a viable approach. – rory.ap Feb 16 '17 at 13:13
  • @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 – Rahul Feb 16 '17 at 13:15
  • 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. – Jatin Feb 16 '17 at 13:29