Bind default value if excel sheet column value null in sql bulk copy upload

Question

I created mvc 4 application that can upload data into database using sql bulk copy upload method.

Once I select excel file, it can upload data into the system.

this is working fine, now I want to add default value if excel field is null

this is how it extract column values of excels

     "s1.Gender, " +
     "s1.Country_of_Birth, " +
     "s1.Date_of_Birth, " +

I want to add default value from code level , I already handle this using database level.

for example to add default value for "date of birth" I added following constraint

ALTER TABLE [dbo].[tbl_HEI_student] ADD  CONSTRAINT [DF_tbl_HEI_student_Date_of_Birth]  DEFAULT (getdate()) FOR [Date_of_Birth]

But when I upload excel file into db ,SQL bulk copy upload method is ignoring adding that default value database .

How can I add default value from controller method

Answer 1

Your problem must be that the column allows NULLS.

In case your column allows NULL and you provide a NULL value on the insert the NULL WILL be inserted. If no value is provided for the column the DEFAULT will be considered.

Setup your column to NOT NULL.

ALTER TABLE [dbo].[tbl_HEI_student] ALTER COLUMN [Date_of_Birth] DATE NOT NULL

Here is the Default Definition.

EDIT:

As you have noticed, you can't BulkInsert the NULLS to that columns and make the DEFAULT value prevail on SqlServer side, and the NOT NULL i suggested will enforce that either no value is supplied or something other than NULL.

I can see a few options.

• Either deal with them .NET side by setting the value to your default (you will have to show you .NET code if you want help with that)
• doing one bulk for rows with value and another for rows without and removing the mapping for that column, the default constraint will take action
• Setting the row back to allow NULLS, do the bulk as you were doing, and doing an update after the bulk to set the NULLS to your default value.

I know BULK INSERT and BCP both have a qualifier as shown here to deal with the NULL values. The same option is available in the .NET SqlBulkCopy options.

Here is another question related to your problem.

Keep in mind your database schema should help you enforce your business rules and keep your data clean. If the date should never be NULL, keep the column as NOT NULL and do your solution accordingly.