Im trying to figure out how I can do bulk operations on a table in SQL datbase where I want to be able to handle failed rows.
Let's say I do a bulk insert of 10 rows into a table and row number 5 has some faulty data and can't be inserted. I then want the 9 other rows to be inserted but in some way be able to get the 1 row that coulden't be inserted in return so I can store this row in some place for later error handling.
It would be great if I could use some nuget package for C# programming.
Typically the most optimal solution is to validate bad data as early in the process as possible. Consider the following strategy.
- Create a datatable in your c# code where the datacolumns precisely match the datatype, length, nullable, etc. of the target tables where the needs to be persisted. If any data does not conform it will throw an exception when attempting to insert into the datatable.
- Populate the datatable from your source data, one data row at a time. If a row fails to insert into the datatable, your exception handling could catch that row and log that data as needed.
- Create a custom table-valued type in SQL Server to match the datatable. Make sure everything matches including all of the meta data of each datacolumn.
- Create a stored procedure on SQL Server that contains the table-values input parameter. This parameter will be the new custom type created in step 3. The table valued parameter can then be used to quickly insert the data into the database tables.
- Pass your datatable as a parameter value to the stored procedure. The data type should be SqlDbType.Structured.
This design adds speed and efficiency with early input validation, while allowing the process to pass data in bulk, allowing for fast inserts.