run by batch - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

run by batch

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-07-05 : 00:45:20

Select Number,ID From TableA
Where Number>= 1 And AutoNo <= 10

how can i run it as batch by batch to update.
number where 1 to 10, then 11-20, then 21-30 and so on..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-05 : 01:38:03

you can use row_number() function for that and use it for selecting batch.
See illustration below


DECLARE @BatchSize int,
@BatchNo int

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY AutoNo) AS Seq,*
FROM Table
)t
WHERE Seq >= ((@BatchNo-1)* @BatchSize) + 1
AND Seq <= @BatchNo * @BatchSize

if using SQL 2012, another option available is OFFSET..FETCH statement as below


SELECT *
FROM table
ORDER BY AutoNo
OFFSET ((@BatchNo-1) * @BatchSize) ROWS
FETCH NEXT (@BatchSize) ROWS ONLY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-07-05 : 02:36:51

it doesnt return any record..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-05 : 02:39:51

quote:
Originally posted by peace

it doesnt return any record..

what values you passed for @BatchSize and @BatchNo?

you need to pass some values to it. I just showed you the stub

for ex:


DECLARE @BatchSize int,
@BatchNo int

SELECT @BatchSize =10,
@BatchNo = 2

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY AutoNo) AS Seq,*
FROM Table
)t
WHERE Seq >= ((@BatchNo-1)* @BatchSize) + 1
AND Seq <= @BatchNo * @BatchSize

if using SQL 2012, another option available is OFFSET..FETCH statement as below


DECLARE @BatchSize int,
@BatchNo int

SELECT @BatchSize =10,
@BatchNo = 2

SELECT *
FROM table
ORDER BY AutoNo
OFFSET ((@BatchNo-1) * @BatchSize) ROWS
FETCH NEXT (@BatchSize) ROWS ONLY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-07-05 : 02:56:18

Select Number,ID From TableA
Where Number>= 1 And Number<= 10

i would like to schedule to update tableB which has millions of record.
thinking to batch it maybe 1-10 then 11-20, 21-30 and so on..
the method gave need to set it manually ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-05 : 03:09:57

quote:
Originally posted by peace

Select Number,ID From TableA
Where Number>= 1 And Number<= 10

i would like to schedule to update tableB which has millions of record.
thinking to batch it maybe 1-10 then 11-20, 21-30 and so on..
the method gave need to set it manually ?

Nope. you can automate it if needed by using a loop

something like


DECLARE @BatchSize int,
@BatchNo int,
@RecordCount int

SELECT @BatchSize =10,
@BatchNo = 1

SELECT @RecordCount=COUNT(*)
FROM TableA

WHILE @BatchSize * @BatchNo <= @RecordCount
OR (@RecordCount BETWEEN  (@BatchSize * (@BatchNo-1))+1
AND (@BatchSize * @BatchNo) )
BEGIN

UPDATE b
SET b.Col1= a.Col1,
..
FROM tablB b
INNER JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY AutoNo) AS Seq,*
FROM TableA
)t
ON t.Col = b.RelatedCol
...
WHERE Seq >= ((@BatchNo-1)* @BatchSize) + 1
AND Seq <= @BatchNo * @BatchSize

SELECT @BatchNo = @BatchNo + 1 
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources