Setting a Primary key to subsequential Data (Header - Line)

Question

Setting a Primary key to subsequential Data (Header - Line)

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

rena24 Default port Points: 1482 More actions · Answer 1

Wow!!! Thats really cool (You are cool :cool::cool::cool:)!! I will try it now!! Thaaaaaaaaaank you very much!!!

It really works and I find this solution so brilliant. I know that sql can do anything and the XML object is really cool!!! Thank you again and you are really amazing!!! I love databases and sql and I really hope that one day I will be so good as you!!! :-):-):-)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

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.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

rena24 Default port Points: 1482 More actions · Answer 3

Hallo again!!

You have right. I must first run the vb code to get the ref_number for each raw and then I can run the cool-xml code!! Without the generated ref_number the xml code doesnt work. As an sql freak I am very excited with this xml code, its really a new way!!! Thanks again!!:-):-):-):-)

And I dont have in the first place a ref_number. My raw data is-->

SELECT 'H;123442;23%;78.99;52000990889'

UNION ALL SELECT 'L;socks;blue;100'

UNION ALL SELECT 'L;socks;red;200'

UNION ALL SELECT 'H;123443;23%;500.99;52000990111'

UNION ALL SELECT 'L;skirt;blue;100'

UNION ALL SELECT 'L;pants;red;200'

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 4

the way i see it there is aprimary key if you split the header from the lists

for examples all rows which start with H would go to a table Header and the one starting with L go to lists

the a simply identity/checksum column or a merger statement shud be good enough to ensure no duplicates.

Jayanth Kurup[/url]

Dwain Camps SSC Guru Points: 86908 More actions · Answer 5

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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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