如何在MSSQL上结合批量插入实现版本控制

Question

我正在使用。net类SqlBulkCopy执行许多记录(通常为100k)的批量插入，到MS SQL Server 2012数据库中。我的表是这样的:

CREATE TABLE [dbo].[Product](
  [Id] [bigint] NOT NULL,
  [Name] [varchar](50) NULL,
  [ProductClassId] [int] NOT NULL,
  [StateDate] [date] NOT NULL,
  [IsActive] [bit] NOT NULL,
);

我需要实现这个逻辑:每个ProductClass只有最新的产品记录是活动的。如果插入了一个产品记录，那么我需要在同一个ProductClass中取消激活先前的活动产品(将IsActive设置为0)，新插入产品的ProductClass也是如此。只有当新插入产品的StateDate高于当前活动产品的StateDate时，我才希望这样做。如果不是，则仍然插入新记录，但IsActive = 0(当前产品保持完整)。

你知道如何有效地做到这一点吗?我想过扳机，但我怕它会很慢。

谢谢

Answer 1

你可以像这样在你的表上创建这个After Insert触发器…

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

Answer 2

诀窍是不要使用SqlBulkCopy将数据移动到最终表中。

创建一个临时表，在那里批量上传，然后用sql语句处理成最终表。在这个过程中，你可以做所有你想做的ETL的东西;)