divide sql server table int chunks

Question

using sql server 2008

I would like to take a table that has 11 million records and divide them into chunks of 50000 each while maintaining the original table and just making sure each chunk contains unique records. If I select top 50000 records for the first batch, how can I guarantee I get the next 50000 and so on.

Answer 1

I'm not sure why you'd want to, but here goes-

SELECT * FROM
( 
SELECT *, ROW_NUMBER() OVER (ORDER BY yourKey) AS Num FROM YourTable
) X WHERE x.Num BETWEEN 0 AND 49999

Then you would increment your 0 and 49,999 either programmatically (hopefully, if you're going this over 11 million records), or manually.

Answer 2

Use the ranking functions, in particular row-number():

DECLARE
  @From  int
 ,@Thru  int

--  Example here would be the second set
SET @From = 50001
SET @Thru = 100000

SELECT <columns>
 from (select <columns>, row_number() over (order by <PrimaryKey>) Ranking
        from MyTable) xx
 where Ranking between @From and @Thru

Answer 3

try NTILE(n), where n=number of chunks. Since you need to create the "chunk" tables, you should know how many chunks you need, and NTILE will split up the rows properly for you:

DECLARE @YourTable table (RowID int, RowValue varchar(5))
INSERT @YourTable VALUES (1,'A')
INSERT @YourTable VALUES (2,'B')
INSERT @YourTable VALUES (3,'C')
INSERT @YourTable VALUES (4,'D')
INSERT @YourTable VALUES (5,'E')
INSERT @YourTable VALUES (6,'F')
INSERT @YourTable VALUES (7,'G')
INSERT @YourTable VALUES (8,'H')
INSERT @YourTable VALUES (9,'I')
INSERT @YourTable VALUES (10,'J')
INSERT @YourTable VALUES (11,'K')
INSERT @YourTable VALUES (12,'L')

DECLARE @YourTable1 table (RowID int, RowValue varchar(5))
DECLARE @YourTable2 table (RowID int, RowValue varchar(5))
DECLARE @YourTable3 table (RowID int, RowValue varchar(5))
DECLARE @YourTable4 table (RowID int, RowValue varchar(5))
DECLARE @YourTable5 table (RowID int, RowValue varchar(5))

INSERT @YourTable1
    SELECT
        RowID, RowValue
        FROM (SELECT
                  RowID, RowValue
                      ,NTILE(5) OVER(ORDER BY RowID) AS TableID
                  FROM @YourTable
             ) dt 
        WHERE dt.TableID=1


INSERT @YourTable2 
    SELECT
        RowID, RowValue
        FROM (SELECT
                  RowID, RowValue
                      ,NTILE(5) OVER(ORDER BY RowID) AS TableID
                  FROM @YourTable
             ) dt 
        WHERE dt.TableID=2

INSERT @YourTable3 
    SELECT
        RowID, RowValue
        FROM (SELECT
                  RowID, RowValue
                      ,NTILE(5) OVER(ORDER BY RowID) AS TableID
                  FROM @YourTable
             ) dt 
        WHERE dt.TableID=3


INSERT @YourTable4 
    SELECT
        RowID, RowValue
        FROM (SELECT
                  RowID, RowValue
                      ,NTILE(5) OVER(ORDER BY RowID) AS TableID
                  FROM @YourTable
             ) dt 
        WHERE dt.TableID=4


INSERT @YourTable5 
    SELECT
        RowID, RowValue
        FROM (SELECT
                  RowID, RowValue
                      ,NTILE(5) OVER(ORDER BY RowID) AS TableID
                  FROM @YourTable
             ) dt 
        WHERE dt.TableID=5

SELECT * FROM @YourTable1
SELECT * FROM @YourTable2
SELECT * FROM @YourTable3
SELECT * FROM @YourTable4
SELECT * FROM @YourTable5

OUTPUT:

RowID       RowValue
----------- --------
1           A
2           B
3           C

(3 row(s) affected)

RowID       RowValue
----------- --------
4           D
5           E
6           F

(3 row(s) affected)

RowID       RowValue
----------- --------
7           G
8           H

(2 row(s) affected)

RowID       RowValue
----------- --------
9           I
10          J

(2 row(s) affected)

RowID       RowValue
----------- --------
11          K
12          L

(2 row(s) affected)