How to add new column in SQL Server using SqlBulkCopy which is not present in Excel using asp.net

asp.net c#-4.0 sqlbulkcopy sql-server

Question

I am creating an application which will upload data from Excel to SQL Server using ASP.NET. I know how to upload Excel data using SqlBulkCopy. But I am trying to upload some extra data for the table column (addeddate, addedby...etc) which is not present in the Excel sheet.

I get this error:

The given ColumnName '18-01-2016 17:24:07' does not match up with any column in data source.

Popular Answer

You can try below solution to insert constant values like addeddate, addedby...etc.

SELECT
    EXCEL_COL1, 
    EXCEL_COL2, 
    'newconstantvalue' as CustomCol 
FROM
    ExcelSheet1

Then

bulkCopy.ColumnMappings.Add("Table_COL3", "CustomCol");

Please refer the article for more detail.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why