Technical Article

Split by a row (count) number of a query results

,

Sometime you need to split the query results row in a raw way, for example split the query results in bundles of 65000 rows.

In this utitlitary script is showed a way to do it.

We first create a temporary table to hold the query result, the table has an identity column that we will use to tract the spliting counting; the rest of the columns have to match the structure of the query result.

We create a loop based on the bundle size required.

Try it.

DECLARE @min INT,
@div INT, 
@count INT,
@buklet INT,
@c INT,
@STR VARCHAR(MAX)

SET @div=65000

-- create temporary table #tbl20090427105421240

CREATE TABLE #tbl20090427105421240(
-- table structure the same as 
-- the query definition, But the ID column

ID Identity(1,1) NOT NULL,
column1 type,
.
.
.

)


INSERT INTO #tbl20090427105421240
SELECT *
FROM << aTable >>

SELECT @min=MIN(ID),@count=COUNT(*) 
FROM #tbl20090427105421240

SELECT @buklet = CAST(@count/@div AS INT)

IF @count%@div>0
BEGIN
SET @buklet=@buklet+1
END

SET @c=0

WHILE @c<@buklet
BEGIN
 SELECT * FROM ##tbl20090427105421240 WHERE ID BETWEEN (@min + @c*@div) AND ((@min + @c*@div) +@div-1)
SET @c=@c+1
CONTINUE
END

Rate

4 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (2)

You rated this post out of 5. Change rating