使用 Row_Number 将对该表的选择结果插入表中

debugcn 发表于 Dev

基础

我正在创建一个查询，在其中选择表上的数据，然后从该表中选择一些行，然后将这些行插入到另一个数据库中的另一个相同的表中，然后重复该过程以从中选择下一个行数原始表。

作为参考，这就是我尝试做的事情（已经为 Oracle 构建了它）：

$" INSERT INTO {destination-table} 
     SELECT * FROM {original-table}
     WHERE ROWID IN (SELECT B.RID 
          FROM  (SELECT ROWID AS RID, rownum as RID2 
                   FROM {original-table}
                   WHERE {Where Claus} 
                   AND ROWNUM <= {recordsPerStatement * iteration}                                              
) B WHERE RID2 > {recordsPerStatement * (iteration - 1)})"

这是通过 .net 中的循环

但是，对于 SQL 服务器，我无法完成此操作。我检索的数据：

$" Select B.* from (Select A.* from (Select Row_NUMBER() 
       OVER (order by %%physloc%%) As RowID, {original-table}.* FROM 
{original-table} where {where-claus}) 
A Where A.RowID between {recordsPerStatement * (iteration - 1)} 
AND {recordsPerStatement * iteration} B"

这里的问题是上面的 select 会产生一个额外的列（ROWID），它阻止我将上述数据插入到目标表中

我一直在寻找摆脱顶部选择中的 ROWID 列的方法，或者根据检索到的数据从原始表中插入数据（类似于插入到目标表中选择 * 从原始表中存在的（其余部分）选择查询）....但无济于事

TLDR = 去掉计算中使用的 ROWID 列，然后才能将行插入到相同的表中

规格：

大量（数百万行）数据（因此以位为单位进行处理）
未知表（所以我不能调用特定的列名，因为它们是未知的）
需要有一个订单（因此是 row_number），所以相同的数据不会被复制两次。
使用选择查询插入（因为首先检索它并在本地执行一些魔术会严重影响性能）
如果可以在此处添加必要的其他变量（如订单子句变量），但是，对查询中数据的任何引用都将始终是变量 + 如果我能找到一种方法来不在查询中添加更多变量，那将是可取的

我希望有人对我可以进一步研究的内容有所了解。

基础

我想出的解决方案：

首先从数据库中读取 column_names 并将它们存储在本地，然后在构建插入/选择查询时再次使用它们，并且只从视图中选择那些列（它们都与 ROWID 分开）。

commandText = $"SELECT column_name
               FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = N'{table}'"

columnNames = "executionfunction with commandText"
columnNamesCount = columnNames.Rows.Count

Dim counter As Int16 = 0
commandText = String.Empty
commandText = $"INSERT INTO {destination} SELECT "

    For Each row As DataRow In columnNames.Rows

         If counter = columnNamesCount - 1 Then
              commandText += $"B.{row("column_name")} "
         Else
              commandText += $"B.{row("column_name")}, "
         End If

         counter = counter + 1
    Next

    commandText += $"FROM 
                   (Select A.* FROM (Select Row_NUMBER() 
                   OVER(order by %%physloc%%) AS RowID, {table}.* 
                   FROM {table} where {filter}) A
                   WHERE A.RowID between ({recordsPerStatement} * ({iteration}-1)) + 1   
                   AND ({recordsPerStatement} * {iteration})) B"

编辑：要删除 %%physloc%% 子句，已经内置了一个 OFFSET FETCH NEXT 部分。新方法：

commandText += $"INSERT INTO {destination} SELECT * FROM {table} WHERE {filter}"

    For i As Int16 = 1 To columnNamesCount

        If i = 1 Then
            commandText += $"ORDER BY {columnNames.Rows(i - 1)("column_name")} ASC"
        Else
            commandText += $"{columnNames.Rows(i - 1)("column_name")} ASC"
        End If

        If i <> columnNamesCount Then
            commandText += ", "
        End If
    Next

commandText += $" OFFSET ({recordsPerStatement} * ({iteration} -1)) ROWS FETCH Next {recordsPerStatement} ROWS ONLY"