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

record.

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.

*/