JJ B. If you're not currently using a loop or the method in the article, what are you using?
I'll tell ya, but you have to be kind (not that you wouldn't be). I put this together several years ago. I didn't have access to rank functions at the time, and I made this up myself. Also, this is run once a night on very few records (less than 50). So, stressing about being terribly efficient didn't make sense.
It is a multi-step stored proc. The proc does use what you call a tally table, but not in the same, most likely more efficient way, as you did. Below are comments copied from my code. If they don't make sense and you are still interested, I could attach the proc - which has more comments and actual SQL. :-)
So, just how is the text broken out?
There were two general possible approaches. I don't know which is faster, but
speed is not a major issue here. I just liked the approach taken here best. It
seems the cleanest.
The approach not taken: Use cursors and a couple embedded loops to go through
each applicable record and then to break out each applicable text chunk in each
The lovable approach used below: Three steps:
a) for each return record, create copies in @RetTable. The number of copies created for all records equals the maximum possible number of chunks needed for
the data being exported now. A simple DataLength() function will tell us
the length of the largest narrative in the data to be exported. If you divide
that number by the ChunkLength and round up, you get the maximum number of
chunks needed (and is the number of duplicate records we want to create in
this step). For example, if the record with the largest narrative will need no more than 3
records/chunks, then all the narrative records will essentially be unioned
to themselves/duplicated 3 times in this step. This data will be stored in our return
table variable, @RetTable.
Note that we give each duplicate record a sequence number using the Number table. In the
example used here, the duplicate allegation narrative records will have
sequence numbers 1, 2 and 3.
b) run a query to delete records which do not have enough text to fill up any
part of that chunk. Continuing the example from above, if allegation 234
has a narrative that takes 3 chuncks/records, then step a) above makes 3
three copies of each allegation, including allegation say 543, which say only
needs two chunks. So, in this step, we delete the record for the record
belonging to the third sequence number for allegation narrative 543. If
any allegations only need one chunk, then we delete the records for the last
sequence numbers. The where clause in our query already eliminates narratives
records without any text.
c) run a query that uses SUBSTRING to make each remaining record only have
the relevant chunk of text needed for that sequence number. For example,
the records belonging to sequence/chunk #1 will delete everything after the
first 8,000 characters. The records belonging to sequence 2 will delete
all data except for characters 8,001 through 16,000.