We help IT Professionals succeed at work.

Outputting records in batches

programmher used Ask the Experts™
I inherited a stored procedure that performs a series of inserts. updates into temp tables, then outputs the entire recordset to a flat file.

I have received a request to process and then output the records in batches of 100.

I know how to count the number of records that will be processed but how do I code the stored procedure to take the first 100 records, perform the inserts and then updates, output those records to the flat file location, then proceed to the next 100 files ?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

What are the total number of rows?  A range is fine.


The total number of rows varies - it could be as many as one or two thousand.  It should never be more than 10,000.
Top Expert 2012

What are you using to output the file?
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Can you show as relevant parts of your stored procedure? For example, it is important if we can perform something like
insert into DestTable (a, b, c) select top 100 a, b, c from SrcTable where Processed != 'T'

Open in new window


acperkins - I am using an SSIS packge to output the file.

Qlemo -

Here is what I have so far:

declare @li_rowcount int
SELECT @li_rowcount = count (*)
FROM [Books].[dbo].[tbl_booktitles]
WHERE AUTHORS IN (SELECT AUTHORS_LastNames FROM tbl_Authors WHERE Active = 1 AND Classics = 1).

print @li_rowcount
if @li_rowcount = 0  
RAISERROR ('No book titles  found.',17,-1 )
ELSE IF @li_rowcount > 250
    print  @li_rowcount --this is where I need logic to return the first 250 rows, then the next 250 rows, etc.
      print 'the row count is less than 250 records'  --proceed with the current process that successfully runs pretty fast as long as the record count is less thn 250.
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
The IN will usually perform badly if many records are fitting from tbl_authors table. A inner join is a better idea here.

There are many approaches. I would use cursor here to retrieve all rows
FROM [Books].[dbo].[tbl_booktitles] bt
join tbl_Authors on AUTHORS = AUTHORS_LastNames 
where Active = 1 AND Classics = 1

Open in new window

and then just count up a var to see which row you process just that moment.