I'm performing a bulk insert of many records (typically 100k) using .NET class SqlBulkCopy, into a MS SQL Server 2012 database. My table looks like this:
CREATE TABLE [dbo].[Product]( [Id] [bigint] NOT NULL, [Name] [varchar](50) NULL, [ProductClassId] [int] NOT NULL, [StateDate] [date] NOT NULL, [IsActive] [bit] NOT NULL, );
I need to implement this logic: Only the newest product record for each ProductClass is active. If a product record is inserted, then I need to deactivate previous active product (set IsActive to 0) in the same ProductClass, as is ProductClass of the newly inserted product. I wish to do that only if StateDate of newly inserted product is higher than StateDate of current active product. If it's not, the new record is still inserted, but with IsActive = 0 (the current product remains intact).
Any idea how to do this effectively? I thought about a trigger, but I'm afraid it would be slow.
The trick is not to use SqlBulkCopy to move data into the final table.
Make a temporary table, bulk upload there, then process into the final table with a sql statement. In this processing you can do all the ETL stuff you want to ;)