时间戳

首页 > 解决方案 > 选择所有数据时sql server删除

sql - 选择所有数据时sql server删除

问题描述

我添加了一个存储过程，它删除和插入特定表的数据，但选择的执行时间可能需要 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

标签： sqlsql-serversql-insertsql-delete

解决方案

一个比喻：

  • 你的桌子上摆满了你在当地农贸市场出售的蔬菜。
  • 当你带来新的蔬菜负载时，你需要 20 分钟才能清理桌子并用更新的产品替换库存。
  • 您不希望客户坐在那里等待 20 分钟以进行转换（大多数人只会从其他人那里购买蔬菜）。

如果您有第二张空桌子，您可以在其中装载新蔬菜，而当您这样做时，顾客仍然可以从第一张桌子购买旧蔬菜怎么办？（让我们假设不是较旧的蔬菜变质或不那么受欢迎。）

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

  • 您有第二个在后台加载的影子表。
  • 在发生这种后台负载时，用户会继续在第一个表中看到较旧的数据。
  • 后台加载完成后，您可以通过以下方式将用户重定向到具有更新数据的第二个表：
    • 改名
    • 更改同义词或视图以指向新表
    • 在模式之间转移（见这里和这里）
    • 分区切换（尽管普遍认为，不需要企业版）

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

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

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中演示了这一点，除了那里的权限不允许我们为阻止程序指定较低优先级的等待选项，这在规模上将很重要。为简单起见，也没有错误处理，但这并不意味着它不是必需的。（感谢@Charlieface 指出PARTITION 1对于非分区表来说这是不必要的。）

你也可以更快地清理垃圾，就像在提交之后一样，但是如果有某种问题，保留它可以让你排除故障或恢复它。

推荐阅读