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

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

Maybe you could prepare a statement that runs after the bulk insert to set the active records? — major-mann, Mar 25 '14 at 20:18

M.Ali · Answer 1 · 2014-03-26T11:16:04.927

0

You can create this After Insert trigger on your table something like this...

CREATE TRIGGER tr_AfterInsert_Set_Active
ON Product
AFTER INSERT
AS
BEGIN
  SET NOCOUNT ON;

   WITH CTE AS
    (
     SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY StateDate DESC) AS RN
     FROM [dbo].[Product]
     )
    UPDATE CTE
    SET [IsActive] = CASE WHEN RN = 1 THEN 1 
                          WHEN RN = 2 THEN 0
                     END 
END

edited Mar 26 '14 at 11:16

answered Mar 25 '14 at 20:21

M.Ali

67,945
13
101
127

Although your solution will probably work it will impact database performance greatly - after every inset the entire table will be inspected ... I would rather execute it once after the bulk import – UV. Mar 26 '14 at 10:46
You are correct I have made a slight change to make it update only the last two rows. or The OP can make a Stored Procedure and Execute it after every Insert but really will be a reliable way of doing this NOPE I dont think so. – M.Ali Mar 26 '14 at 11:18

score 0 · Answer 2 · answered Mar 26 '14 at 11:18

0

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

answered Mar 26 '14 at 11:18

TomTom

61,059
10
88
148

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

2 Answers2