存储过程 - 截断表 [英] Stored procedure - truncate table

问题描述

我创建了一个存储过程来向表中添加数据.在模拟方式中，步骤是:

I've created a stored procedure to add data to a table. In mock fashion the steps are:

• 截断原始表
• 选择数据放入原表

将数据选入原始表的查询很长(可能需要将近一分钟才能完成)，这意味着该表随后一分钟内没有数据.

The query that selects data into the original table is quite long (it can take almost a minute to complete), which means that the table is then empty of data for over a minute.

为了修复这个空表，我将存储过程更改为:

To fix this empty table I changed the stored procedure to:

• 选择数据到#temp 表
• 截断原始表
• 将#temp 中的 * 插入到 Original 中

当存储过程运行时，我在原始表上做了一个 select * 并且它是空的(刷新，它一直是空的，直到存储过程完成).

While the stored procedure was running, I did a select * on the original table and it was empty (refreshing, it stayed empty until the stored procedure completed).

截断是否发生在程序的开头，不管它实际上在代码中的哪个位置?如果是这样，我还能做些什么来控制何时删除数据?

Does the truncate happen at the beginning of the procedure no matter where it actually is in the code? If so is there something else I can do to control when the data is deleted?

推荐答案

sp 是由 HTTP Get 中的代码调用的，所以我不希望表在刷新期间空了超过一分钟.当我问这个问题时，我正在使用表中的 select * 进行测试，但刚才我通过点击 postman 中的端点进行测试，但从未收到空响应.因此，似乎将截断稍后放在 sp 中确实有效.

The sp is being called by code in an HTTP Get, so I didn't want the table to be empty for over a minute during refresh. When I asked the question I was using a select * from the table to test, but just now I tested by hitting the endpoint in postman and I never received an empty response. So it appears that putting the truncate later in the sp did work.

这篇关于存储过程 - 截断表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！