选择所有数据后，Sql服务器将删除 [英] sql server delete when all data are selected

问题描述

我添加了一个存储过程，用于删除和插入特定表的数据，但SELECT的执行时间可能需要10分钟才能完成，因此在此期间我的表是空的

如何修改我的存储过程以在它完成时选择所有需要的数据？在目标表上删除当前数据并插入所选数据？

这是我的代码

delete from table_b

insert into table_b(id,name,km)
    select id,t.name,t.kmfrom table_a
    OUTER APPLY (select * from dbo.calculate(table_a.CoordonneeX,table_a.CoordonneeY)) as t

推荐答案

打个比方：

• 你在当地农贸市场卖的蔬菜摆满了桌子。
• 当你带来一批新的蔬菜时，你需要20分钟来清理桌子，用新的产品取代库存。
• 您不希望客户坐在那里等20分钟等待切换发生(大多数人只会从其他人那里购买蔬菜)。

如果您有第二张空桌子，在那里您装满了新蔬菜，而在这样做的同时，客户仍然可以从第一张桌子上买到较旧的蔬菜，情况会怎样？(让我们假设这不是因为旧蔬菜变坏了，或者其他原因不太受欢迎。)

有多种方法可以满足您的场景。基本概念是：

• 您有第二个在后台加载的影子表。
• 当此后台加载发生时，用户将继续看到第一个表中较旧的数据。
• 后台加载完成后，您可以通过以下方式将用户重定向到数据更新的第二个表：
  • 重命名
  • 更改同义词或视图以指向新表
  • 在架构之间传输(请参阅here和here)
  • partition switching(尽管普遍认为，不需要企业版)

我一直喜欢使用模式来解决这一问题，但分区切换是最好的解决方案，因为其他三种解决方案需要更积极的模式修改锁，不允许以较低的优先级等待，并且有更大的风险使任何现有的统计/执行计划无效或不那么准确。所有这些解决方案的一个问题是，如果有指向主表的外键，则无论您的计划的一部分是清空父表，都必须处理这一问题。

大量借用Kendra的gist，因为我很懒，而她在那里做得很好，让我们创建原始表的两个副本，一个用于处理传入的新数据，另一个用于接受旧数据：

CREATE TABLE dbo.MyTable
(
  id int NOT NULL,
  description varchar(32)
);

INSERT dbo.MyTable(id, description) VALUES(1, 'old data');

CREATE TABLE dbo.MyTable_Staging
(
  id int NOT NULL,
  description varchar(32)
);

CREATE TABLE dbo.MyTable_Garbage
(
  id int NOT NULL,
  description varchar(32)
);

SELECT * FROM dbo.MyTable;

(这是一个非常简单的模型--当然，您的实际表会有匹配的主键、索引、约束等。)

现在，我们可以在后台加载临时表，完成后，将当前数据切换到垃圾表，然后将临时表切换到主表。

-- perform the background loading outside of any blocking transaction:

TRUNCATE TABLE dbo.MyTable_Garbage;
TRUNCATE TABLE dbo.MyTable_Staging;
INSERT dbo.MyTable_Staging(id, description) 
  VALUES(1, 'new data'),(2, 'new row!');

BEGIN TRANSACTION;

  ALTER TABLE dbo.MyTable
    SWITCH TO dbo.MyTable_Garbage
    WITH ( WAIT_AT_LOW_PRIORITY 
      ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)
  );  

  ALTER TABLE dbo.MyTable_Staging
    SWITCH TO dbo.MyTable;  
    
COMMIT TRANSACTION;
    
SELECT * FROM dbo.MyTable;

我在db<>fiddle中演示了这一点，只是那里的权限不允许我们为拦截器指定等待较低优先级选项，这在规模上将是重要的。为了简单起见，也没有错误处理，但这并不意味着不需要它。(感谢@CharlieFaces指出，PARTITION 1对于未分区的表是不必要的。)

您也可以更快地清理垃圾，就像在提交后立即清理垃圾一样，但保留垃圾可以让您在出现某种问题时进行故障排除或恢复。

这篇关于选择所有数据后，Sql服务器将删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！