从块中的表中选择数据，执行查找并插入到另一个表中 [英] Select data from a table in chunks, perform lookup and insert into another table

问题描述

这个问题突然出现在与性能调优有关的问题上

将SP写入性能明智以从表中获取数据的最佳方法是什么？ ，执行查找并插入另一个表格。

重复此过程，直到主表完全遍历。



我是什么尝试过：



我知道我们可以使用

临时表，

临时变量或SP中的视图实现了这一点，但最好的方法是什么。

this question hits to my mind related to performance tuning that
What is the best way of writing a SP to performance wise to pick up data in chunks from a table, Perform look up and insert into another table.
repeat the process until the main table is traversed completely.

What I have tried:

I know we can use
temp table,
temp variables or views in SP to achieve this but what would be the best way.

推荐答案

在表格之间移动数据的最简单方法之一是：

One of the simplest ways to move data between tables in chunks is:

DECLARE @CHUNK_SIZE int = 10000;
DECLARE @RC int = @CHUNK_SIZE;

WHILE @RC >0
BEGIN
    INSERT INTO TargetTable (Mycolumns)
    SELECT  TOP @CHUNK_SIZE Mycolumns
    FROM    SourceTable
    WHERE   Conditions
    ;
    SET @RC = @@ROWCOUNT
    ;
END

条件需要排除已处理的行。

性能主要取决于这些条件（和索引）。

您可以使用 NOT EXISTS ，或使用有序查询并设置变量= Max（ID）来自输出子句，或者你可以从Sourcetable中删除已处理的行。

但是你已经被告知，我们没有足够的信息告诉你最好的方法。

Where the conditions need to exclude already processed rows.
The performance is mostly depending on these conditions (and indexes).
You can for example use NOT EXISTS, or use an ordered query and set a variable = Max(ID) from the output clause, or you can remove processed rows from the Sourcetable.
But as you've already been told, we don't have enough information to tell you the best way.

这篇关于从块中的表中选择数据，执行查找并插入到另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！