How to implement versioning in combination with bulk insert on MSSQL?

.net sqlbulkcopy sql-server tsql

Question

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.

Thank you

Popular Answer

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 ;)




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why