Bind default value if excel sheet column value null in sql bulk copy upload c# sqlbulkcopy sql-server


I developed a mvc 4 application that uses the sql bulk copy upload technique to upload data to databases.

Once I choose an excel file, the system may upload data.

Everything is functioning OK, however I'd want to set a default value if the excel field is empty.

This is how Excel column values are extracted.

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

I currently manage this at the database level, but I want to set a default value at the code level.

for instance, I applied the following restriction to provide a default value for "date of birth."

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

However, SQL bulk copy upload technique ignores adding that default value database when I upload an excel file to a database.

How can I use the controller method's default value?

6/15/2015 9:59:52 AM

Popular Answer

The fact that the column accepts NULLS must be your issue.

If your column supports NULL and you enter a NULL value, the characters WILL will be inserted. In the absence of a value for the column, the DEFAULT shall be taken into account.

Set the NOT NULL option for your column.

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

I give you the Standard Definition.


You've probably observed that you can't bulk insert NULL values into those columns to compel SQL Server to use the default value; instead, the NOT NULL option I mentioned would cause SQL Server to accept either no value or a value other than NULL.

I see a couple possibilities.

  • Set the value to your default to deal with them on the.NET side (you will have to show you .NET code if you want help with that)
  • The default constraint will act by performing two bulk operations—one for rows with values and another for rows without—and deleting the mapping for that column.
  • Setting the row back to accept NULLS, doing the bulk as you normally would, then performing an update to change the NULLS to your default value.

I am aware that the qualifiers BUCKING INSERT and BCP both provide a way to deal with NULL values, as shown by here. The.NET Options for SqlBulkCopy offers the same choice.

Another query relating to your issue is Here.

Remember that your database structure should assist you in enforcing your business rules and maintaining accurate data. Keep the field as NOT NULL and adjust your solution if the date should never be NULL.

5/23/2017 12:14:28 PM

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow