Jeffrey Williams-493691 (5/2/2010)
WayneS (5/2/2010)
Digs (5/1/2010)
Jeff,All works fine: And Fast !
I will keep your name on my list when I need to spend $$$ on my project .
Minutes have gone down to seconds !!:-):-D:-P;-):w00t::cool::hehe:
Jeff must be slipping :-D... usually when he gets involved, minutes go down to milliseconds! :w00t:
Nah - he managed a solution in milliseconds (bcp), but was constrained by the OP's requirements. 😎
Heh... thanks guys and true enough on the constraints here... the real killer is VARCHAR(MAX) on this one. Just using that causes double the duration even when the number of characters is less than 8000. I know you'll think that I've tanked but Phil Factor has shown that a very tight While Loop on things that use VARCHAR(MAX) will sometimes outperform a Tally Table solution for VARCHAR(MAX) datatypes.
As we've seen on dozens of posts, an XML splitter would not have performed so well.
And finally, the one thing I don't use would probably be the best solution here (other than loading it from a file using BULK INSERT, of course) but the OP can't use that either... a very well written CLR to do the split.
--Jeff Moden
Change is inevitable... Change for the better is not.