Evil Kraig F (9/18/2012)
dwain.c (9/18/2012)
Now I'm fairly sure someone is going to think that I'm crazy and perhaps I am, but why not use XML for this?Let's set up the incoming data just slightly differently:
:hehe: Jeez, Louise! What was I thinking! :w00t:
I must have too much spare time on my hands.
Dwain, that's pretty cool, but a question. How do you confirm you're getting the information out of the table in the same order it was in the file? Due to the lack of confirmed ordering, I fear this might be dangerous.
HOWEVER... errr, no, that's right, no SSIS. BUT, you could deliver the fully built XML from your custom code to SQL, and then run with Dwain's solution... which I admit is pretty frickin' neat.
Also, you can use XML as a datatype for a proc, so you would simply call the proc with the code-constructed XML and go from there.
You've gotta have the reference number for sure. I do believe you need to be sure the H record comes before the D records also. Ordering of the D records is also not confirmed because there's no detail line number, however there is a placeholder in the code for where one could be applied (look for ORDER BY (SELECT NULL)).
Glad you all found my attempt inteesting at least. 😀
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St