Insert into table the outcome of a select on that table using Row_Number

Question

I am creating a query where in I select data on a table, then select a number of rows from that table, to then insert those rows into another identical table in another Database, and then repeat the proces to select the next number of rows from the orignal table.

For Reference, this is what i try to do (already build it for 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)})"

This is put through a loop in .net

For SQL server however I fail to get this done. The data i retrieve with:

$" 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"

The problem here is that above select produces an extra column (ROWID) which prevents me from inserting the above data into the destination-table

I have been looking at ways to get rid of the ROWID column in the top select or to insert data from original-table based on the data retrieved (something like insert into destination-table select * from original-table where exists in (rest of select query)..... but to no avail

TLDR = Get rid of a ROWID column used in calculations to then be able to insert rows into an identical table

specifications:

• A LOT (millions of rows) of data (therefor processing it in bits)
• Unknown tables (so i cannot call on specific column names, as they are unknown)
• needs to have an order (thus the row_number) so the same data is not copied twice.
• insert using a select query (as first retrieving it and doing some magic locally would severly impact performance)
• If necessary additional variables can be added in here (like an order claus variable) however, any reference to data in the query will ALWAYS be a variable + If I can find a way to not add more varriables in the query then that would be preferable

I hope that someone would have an idea on what i could look at further.

Answer 1

The solution i came up with:

First reading the column_names from the database and storing them locally, to then use them again in building up the insert / select query and only select those columns from the view (which are all apart from 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"

EDIT: To remove the %%physloc%% clause AN OFFSET FETCH NEXT part has been build in. new approach:

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"

Answer 2

This approach uses a temporary table to save the paginated data before processing it page by page. It has worked for me, but not sure if you might have problems with very large data sets. You could put the whole thing in an SP then call the SP with parameters from .net. You will need to add a parameter for the destination table name and construct/execute an INSERT statement in the final loop.

        -- Parameters
        DECLARE @PageSize integer = 100;
        DECLARE @TableName nVarchar(200) = 'WRD_WordHits';
        DECLARE @OrderBy nVarchar(3000) = 'WordID'

        STEP_010: BEGIN   
        -- Get the column definitions for the table

            DECLARE @Cols int;
            SELECT TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
                   , IS_NULLABLE
            INTO #Tspec
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = @TableName;

            -- Number of columns
            SET @Cols = @@ROWCOUNT;
        END;

        STEP_020: BEGIN  
            -- Create the temporary table that will hold the paginated data
            CREATE TABLE #TT2 ( PageNumber int, LineNumber int, SSEQ int )
            DECLARE @STMT nvarchar(3000);


        END;

        STEP_030: BEGIN
        -- Add columns to #TT2 using the column definitions
            DECLARE @Ord int = 0;
            DECLARE @Colspec nvarchar(3000) = '';
            DECLARE @AllCols nvarchar(3000) = '';
            DECLARE @ColName nvarchar(200) = '';


            WHILE @Ord < @Cols BEGIN

                        SELECT @Ord = @Ord + 1;

                        -- Get the column name and specification
                        SELECT @ColName = Column_Name
                                , @Colspec = 
                                     Column_Name + ' ' + DATA_TYPE + CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN ''
                                         ELSE '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS varchar(30) ) + ')' END

                            FROM #Tspec WHERE ORDINAL_POSITION = @Ord;

                         -- Create and execute statement to add the column and the columns list used later
                         SELECT @STMT = ' ALTER TABLE #TT2 ADD ' + @Colspec + ';' 
                                , @AllCols = @AllCols + ', ' + @ColName ;
                         EXEC sp_ExecuteSQL @STMT;

            END;

            -- Remove leading comma from columns list
            SELECT @AllCols = SUBSTRING(@AllCols, 3, 3000);
            PRINT @AllCols

            -- Finished with the source table spec
            DROP TABLE #Tspec;

        END;

        STEP_040: BEGIN -- Create and execute the statement used to fill #TT2 with the paginated data from the source table

            -- The first two cols are the page number and row number within the page
            -- The sequence is arbitrary but could use a key list for the order by clause

            SELECT @STMT = 
                         'INSERT #TT2
                        SELECT FLOOR( CAST( SSEQ as float) /' + CAST(@PageSize as nvarchar(10)) + ' ) + 1 PageNumber, (SSEQ) % ' + CAST(@PageSize as nvarchar(10)) + ' + 1 LineNumber, * FROM
                        ( 

                                SELECT ROW_NUMBER() OVER ( ORDER BY ' + @OrderBy + '  ) - 1 AS SSEQ, * FROM ' + @TableName + ' 

                        )
                            A; ' ;
            EXEC sp_ExecuteSQL @STMT;

            -- *** Test only to show that the table contains the data
                    --SELECT * FROM #TT2;
                    --SELECT @STMT = 'SELECT NULL AS EXECSELECT, ' + @AllCols + ' FROM #TT2;' ;
                    --EXEC sp_ExecuteSQL @STMT;
            -- *** 
        END;

        STEP_050: BEGIN -- Loop through paginated data, one page at a time.
            -- Variables to control the paginated loop
            DECLARE @PageMAX int;
            SELECT @PageMAX = MAX(PageNumber) FROM #TT2;
            PRINT 'Generated ' + CAST( @PageMAX AS varchar(10) ) + ' pages from table';

            DECLARE @Page int = 0;

            WHILE @Page < @PageMax BEGIN
                SELECT @Page = @Page + 1;

                -- Create and execute the statement to get one page of data - this could be any statement to process data page by page
                SELECT @STMT = 'SELECT ' +  @AllCols  + ' FROM #TT2 WHERE PageNumber = ' + CAST(@Page AS Varchar(10 )) + ' ORDER BY LineNumber '

                -- Execute the statment.
                PRINT @STMT -- For testing
                --EXEC sp_EXECUTESQL @STMT;

            END;

            -- Finished with Paginated data
            DROP TABLE #TT2;

        END;