SQL Server: scenarios handling during bulk insert with identity column and custom scenarios messages
I'm prototyping a solution and I'm writing unit tests and I'm stuck trying to I've searched everywhere and can't find a clear answer..... I've been struggling with this for a few days now and could really use some help. I've searched everywhere and can't find a clear answer. I've been banging my head against this for hours. I'm trying to perform a bulk insert into a SQL Server 2017 table that has an identity column, but I'm running into issues when some of the rows contain duplicate keys. Instead of the usual behavior, I would like to capture custom behavior messages and skip those rows while still inserting others. I've tried using `TRY...CATCH` blocks, but it doesn't seem to work effectively for bulk operations. Here’s the relevant part of my code: ```sql BEGIN TRY BULK INSERT MyTable FROM 'C:\path\to\your\file.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2 ); END TRY BEGIN CATCH PRINT 'behavior occurred: ' + ERROR_MESSAGE(); END CATCH ``` When I run this, if there’s a duplicate key behavior, it aborts the entire operation instead of skipping the problematic rows. I've also considered using a staging table, but that seems cumbersome and not ideal for performance. Is there a way to handle this situation better, perhaps by filtering out duplicates before the bulk insert or by altering the behavior handling strategy? Any advice on best practices for this kind of scenario would be greatly appreciated. Any help would be greatly appreciated! How would you solve this? This issue appeared after updating to Sql 3.10. Thanks for your help in advance! The project is a REST API built with Sql. I'm working on a desktop app that needs to handle this. Cheers for any assistance! I'm working with Sql in a Docker container on macOS.