MS SQL 2016下实现Staging表规范化：一行转六行并归档删除

对于你这个需求，集合操作绝对是最优解——SQL Server天生擅长处理批量数据，逐行循环不仅低效，还容易带来事务一致性问题，完全没必要。针对MS SQL 2016，我们可以用CROSS APPLY结合VALUES子句来快速将宽表转成窄表，再配合事务保证数据的原子性。

完整解决方案代码 #

BEGIN TRANSACTION;

-- 将Staging表的单行拆分为6行插入Archive
INSERT INTO Archive (LOTCODE, PARTLOCATION, PARTSTATUS)
SELECT
    s.LOTCODE,
    part_data.PARTLOCATION,
    part_data.PARTSTATUS
FROM Staging s
CROSS APPLY (
    -- 把6个PART状态列转成多行数据
    VALUES
        (1, s.Part1Status),
        (2, s.Part2Status),
        (3, s.Part3Status),
        (4, s.Part4Status),
        (5, s.Part5Status),
        (6, s.Part6Status)
) AS part_data (PARTLOCATION, PARTSTATUS);

-- 删除Staging中已处理的行
DELETE FROM Staging;

COMMIT TRANSACTION;

关键细节说明 #

• 行转换逻辑：CROSS APPLY配合VALUES相当于把每行的6个状态列“展开”成6条独立的记录，完美匹配Archive表的结构。对于你给出的示例数据，这会自动生成6行ABCDE对应位置1-6的状态记录。
• 事务保证一致性：用BEGIN TRANSACTION和COMMIT包裹插入和删除操作，确保要么两步都成功，要么都回滚——避免出现“数据已插入Archive但Staging行没删除”或者反过来的异常情况。
• 性能优势：这种集合操作的效率远高于逐行循环，你每小时1440行的规模完全不在话下，哪怕后续数据量增长，SQL Server也能轻松处理。

可选优化：处理特定时间范围的数据 #

如果你的Staging表会持续累积数据，每次只需要处理最近一小时新增的行，可以给Staging表加个时间戳字段（比如CreatedTime DATETIME DEFAULT GETDATE()），然后在查询中添加过滤条件：

-- 插入时过滤最近一小时的数据
INSERT INTO Archive (LOTCODE, PARTLOCATION, PARTSTATUS)
SELECT
    s.LOTCODE,
    part_data.PARTLOCATION,
    part_data.PARTSTATUS
FROM Staging s
CROSS APPLY (
    VALUES
        (1, s.Part1Status),
        (2, s.Part2Status),
        (3, s.Part3Status),
        (4, s.Part4Status),
        (5, s.Part5Status),
        (6, s.Part6Status)
) AS part_data (PARTLOCATION, PARTSTATUS)
WHERE s.CreatedTime >= DATEADD(HOUR, -1, GETDATE());

-- 删除时也对应过滤
DELETE FROM Staging
WHERE CreatedTime >= DATEADD(HOUR, -1, GETDATE());

这样就能精准处理每小时的新增数据，不会误删之前已经处理过的行。

内容的提问来源于stack exchange，提问作者JPoole