2

I have a SQL statement which returns a number of rows and these are inserted into an array within VBA. I then loop through the array and paste into spreadsheet rows.

I am running out of memory, therefore I would like to do the pasting in batches.

I do not mind running the same SQL query each time to collect the data, so long as I can avoid the memory problems. How can I do this? In My SQL I used to use LIMIT, but here I want to say select the first... 1000? and then the next 1000 etc

intrigued_66
  • 16,082
  • 51
  • 118
  • 189

2 Answers2

1

To "select the first... 1000? and then the next 1000" you do the following:

SELECT * FROM table
LIMIT <limit>
OFFSET <batch * limit>

Where limit would equal 1000 and your batch would start at 0 and increment by 1 for each call

David Lackey
  • 33
  • 1
  • 8
  • What exactly does this do? I call the SQL code multiple times? How/what does it keep track of position with? Thanks for answering – intrigued_66 Jun 20 '12 at 15:11
  • this will get 1000 rows at a time that you can release from memory once you are done (for each batch). You could simply do a for loop to store the batch: limit = 1000; for (batch = 0; batch < total_rows / limit; batch++) { see above } – David Lackey Jun 20 '12 at 15:30
  • 3
    This is Mysql specific - OP specifies SQL Server in the question. – Bridge Jun 20 '12 at 15:33
  • Yes I was after SQL Server- I just related to MySQL's LIMIT ability. – intrigued_66 Jun 20 '12 at 15:40
  • See http://stackoverflow.com/questions/971964/limit-10-20-in-sqlserver . And make sure you have an ORDER clause so you don't get any of the same stuff twice. – Chud Jun 20 '12 at 15:50
1

I've found it easiest to create a stored proc to call that uses a basic pagination function by passing in 2 parameters: the rows per page and requested page.

Check out this question: How to do pagination in SQL Server 2008

If you're calling the stored proc programmatically, you can use a while loop until you don't get any results back.

Community
  • 1
  • 1
Nick Vaccaro
  • 5,428
  • 6
  • 38
  • 60