StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POT-SQL Query to process data in batches without breaking groups
text
Body
copied!<p>I am using SQL 2008 and trying to process the data I have in a table in batches, however, there is a catch. The data is broken into groups and, as I do my processing, I have to make sure that a group will always be contained within a batch or, in other words, that the group will never be split across different batches. It's assumed that the batch size will always be much larger than the group size. Here is the setup to illustrate what I mean (the code is using Jeff Moden's data generation logic: <a href="http://www.sqlservercentral.com/articles/Data+Generation/87901" rel="nofollow">http://www.sqlservercentral.com/articles/Data+Generation/87901</a>)</p> <pre><code>DECLARE @NumberOfRows INT = 1000, @StartValue INT = 1, @EndValue INT = 500, @Range INT SET @Range = @EndValue - @StartValue + 1 IF OBJECT_ID('tempdb..#SomeTestTable','U') IS NOT NULL DROP TABLE #SomeTestTable; SELECT TOP (@NumberOfRows) GroupID = ABS(CHECKSUM(NEWID())) % @Range + @StartValue INTO #SomeTestTable FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2 </code></pre> <p>This will create a table with about 435 groups of records containing between 1 and 7 records in each. Now, let's say I want to process these records in batches of 100 records per batch. How can I make sure that my GroupID's don't get split between different batches? I am fine if each batch is not exactly 100 records, it could be a little more or a little less.</p> <p>I appreciate any suggestions!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload