How to implement versioning in combination with bulk insert on MSSQL?
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.