CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user906780
user906780

Reputation:

SQL split a result properly

I have a result from a request that gives me thousands of lines, and i want to split it into little temporary tables of 10k rows each, with an incremental number from 1 up to 10 000. Lets say i have a big query of 25 000 rows, what i want is to split it in 2 tables of 10k and a third table of 5k. Of course I dont know that exact number of pages i will have to split into.

My first query with all the rows is named [#DECLARATION] and have a number NumeroDeclaration that is 0 for page 1, 1 for page 2, etc. to 9. Each 10 000 of rows it is incremented from 0 to 10 000.

at this time my request looks like this:

    --Page 1
    SELECT * INTO [#PAGE1] FROM [#DECLARATION] WHERE NumeroDeclaration =0 ORDER BY NumeroTransaction;
        --Numerotation
        WITH upd AS (SELECT NumeroDeclaration, ROW_NUMBER() OVER (ORDER BY NumeroTransaction) AS Numtemp FROM [#PAGE1])
        UPDATE upd SET NumeroDeclaration = Numtemp
    --Page 2
    SELECT * INTO [#PAGE2] FROM [#DECLARATION] WHERE NumeroDeclaration =1 ORDER BY NumeroTransaction;
        --Numerotation
        WITH upd AS (SELECT NumeroDeclaration, ROW_NUMBER() OVER (ORDER BY NumeroTransaction) AS Numtemp FROM [#PAGE2])
        UPDATE upd SET NumeroDeclaration = Numtemp
    --Page 3
    --etc.... to Page 10...

I have to do this over 10 pages, and it works, but is there another shorter/classier way to do it?

Im using SQL SERVER 2008 Thanks for reading

Upvotes: 1

Views: 1230

Answers (1)

Gabriele Petrioli
Gabriele Petrioli

Reputation: 196286

I am still not sure why you need to split to smaller tables and you can't use filters on the single [#DECLARATION] table.

You queries imply that the [#DECLARATION] table is already partitioned to pages through the NumeroDeclaration column.

You could do

Declare @recordsPerPage AS int
SELECT @recordsPerPage = 10000;
SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY NumeroDeclaration ORDER BY NumeroTransaction ASC) AS Tempnum,
    NumeroDeclaration+1 AS Page
FROM numbered

This will bring a resultset with embedded page page column and Tempnum column which will reset for each page.

Hope this helps

Upvotes: 2

Related Questions