Add text when using SQLBulkCopy to SQL Server from excel

Question

I've created a page where our partners can upload excel files with statistics using SQLBulkCopy. The file has multiple sheets and I need to add the name of the sheet into a column in the DB for each row. The names of the sheets will not change so hardcoding the names is ok. How can I solve this?

Answer 1

As the name of the sheets doesn't change I solved it this way:

1. Created a temporary table with the same layout as the final.

2. Imported the data from the first sheet to temporary table.

3. Filled the column which was now null with the correct value.

4. Did nr 2->3 for the next sheet until all sheets were imported.

5. Checked the temporary table for errors.

6. Imported the data from the temporary table to the final table one row at the time to be able to spot if the records already exist and if so update it, else insert it.