Insert empty row on SQL Server with BULK INSERT

Question

The example table contains Id fields (table identifier, integer); Name (a simple attribute that allows null values ​​is a string)

I am trying to create a CSV that contains this:

1

1 ""

1, ''

None of them give me an empty string as a result of bulk insertion. I am using SQL Server 2012.

What can I do?

Answer 1

As far as I know, bulk insertion cannot insert an empty string, it can either save null or use the default value with the keepnulls option or without the keepnulls option. For your three sample records, after inserting the database, it should be like:

  |  id |  name 
     |  1 |  Null 
     |  1 |  ""   
     |  1 |  ''   

The reason is that the bulk insert will process your first row, the second column value as null; for the other 2 rows will take the second value of the column as non-zero, and take it as is.

Instead of letting Bulk Insert insert an empty row value for you, you can let the table column have a default value as an empty row.

Example: <Preview> <code> CREATE TABLE BulkInsertTest (id int, name varchar (10) DEFAULT '')

  Bulk Insert same CSV file into table 

 BULK INSERT Adventure.dbo.BulkInsertTest FROM '....\test.csv' WITH ( FIELDTERMINATOR ='\,', ROWTERMINATOR ='\n' ) SELECT * FROM BulkInsertTest 

The result will look like this: (The first line in your CSV will get an empty line)

  |  id |  name 
     |  1 |   
     |  1 |  ""   
     |  1 |  ''   

Answer 2

Please note that the specified DEFAULT value will only be inserted if you do not use the KEEPNULLS parameter. Using the same example as above, if you added the KEEPNULLS option to BULK INSERT , that is:

 BULK INSERT BulkInsertTest FROM '....\test.csv' WITH ( FIELDTERMINATOR ='\,', ROWTERMINATOR ='\n', KEEPNULLS ) 

will ignore the default column value and add NULL to empty rows, i.e.:

 SELECT * FROM BulkInsertTest 

will now give you:

 id name 1 NULL 1 "" 1 '' 

There seems to be no good reason to add KEEPNULLS to your example, but I ran into a similar problem only now, where KEEPNULLS is required in BULK INSERT .

My decision was to make the column [name] in the staging table BulkInsertTest NOT NULL , but remember that the value of the DEFAULT column is ignored and an empty row is inserted instead.

Read more here: Keep Nulls or UseDefault Values ​​During Bulk Import (SQL Server)