Jeff Moden (12/4/2012)
Im coming here to get some options for breaking up a data load. the situation is that i have to expand a set of numbers to cover every number from 000 to 999 (1 becomes 1000-1999). there are 258 thousand input numbers and if i just do a cross apply to the numbers table it causes the log to balloon (a problem because i dont want to have a "one time shrink" every month). This is being loaded into a VARCHAR(16) column which is why the data types are CHAR()
I have to ask.... why are you creating 1,000 rows of storage for every row of input? What is the business reason for that? I'm asking so I can see if there's an alternative to loading so much data that might not get used.
its a business rule. we are expanding blocks of phone numbers where we get the first 7 of the phone number and need every number in the block. currently our dev team is in the process of trying to avoid this process by changing the code but as i have something in place that allows us to keep using the old method other fires are getting put out first.
in the old method our input file all ready had the blocks expanded when the provider sent us the data. this method was the easiest to get in place to contain the issue so we could look at it another day.
For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden
for the best way to ask your question.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw
Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2