CODEWITHSUNDEEP
sql-serverbulkinsert
Exocomp
Exocomp

Reputation: 1537

SQL Server Bulk Insert throw error on null when column does not allow null

In SQL Server (I'm using 2016) there is a feature called Bulk Insert which allows you to insert records from a flat file.

When a field in the flat file is null and the field in SQL Server is not marked to allow nulls then Bulk Insert will silently use an empty string during the insert operation.

Is there a way to override this behavior so that when the field is null it uses null instead of empty string. And because the field is not set for null for it to then throw an error?

Why am I asking for this? Well because fields silently get an empty string value inserted and I don't want that.

NOTE: I'm strictly referring to use T-SQL using BULK INSERT (not bcp or any other means)

NOTE: I've seen Keep Nulls or Use Default Values During Bulk Import (SQL Server), what I've described here I could not find in that document. If you feel it does include what I seek, please point out what I'm missing instead of directing me to that document. Thank you.

NOTE: Here is simple example (note this is not the table I'm working with just simple example to get the point across)

Table Definition:

CREATE TABLE [dbo].[TableOne](
    [Id] [int] NOT NULL,
    [Name] [varchar](10) NOT NULL
)

Bulk Insert Command

BULK
  INSERT TableOne
      FROM 'R:\TableOne.txt'
            WITH(
                  DATAFILETYPE = 'widechar',
                  FIELDTERMINATOR = '\t',
                  ROWTERMINATOR = '\n'
                )

File

1

So basically in this example it is: 1[Tab][CRLF], where the second column is name and it is null.

Result

enter image description here

Upvotes: 0

Views: 3193

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28809

You can't do this from just the BULK INSERT -- this behavior is documented and there's no option that changes things, as far as I can tell:

When importing into a character column that is defined with a NOT NULL constraint, BULK INSERT inserts a blank string when there is no value in the text file.

There's no TREAT_BLANK_AS_NULL option or somesuch that would help this scenario along.

The only solution I can think of is adding the constraint and using CHECK_CONSTRAINTS in BULK INSERT. If you are not comfortable marking the column with this constraint permanently (because other scenarios might have a legitimate need to insert empty values or they're already there?) you could add it temporarily:

ALTER TABLE TableOne WITH NOCHECK ADD CONSTRAINT CK_Name_NotEmpty CHECK ([Name] <> '');
BEGIN TRY
    BULK INSERT TableOne FROM 'C:\Temp\a.txt' WITH (
        DATAFILETYPE = 'widechar',
        FIELDTERMINATOR = '\t',
        ROWTERMINATOR = '\n',
        CHECK_CONSTRAINTS
    )
END TRY
BEGIN CATCH
    ALTER TABLE TableOne DROP CONSTRAINT CK_Name_NotEmpty;
    THROW;
END CATCH
ALTER TABLE TableOne DROP CONSTRAINT CK_Name_NotEmpty;

Obviously this gets a little uncomfortable if the table is dynamic, you need to treat many columns this way, or you can't actually afford to check constraints. At that point I'd recommend using a proper ETL solution, like an SSIS package.

Upvotes: 2

Related Questions