InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 09:51:05 GMT20http://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspx[quote][b]Evil Kraig F (9/18/2012)[/b][hr][quote][b]dwain.c (9/18/2012)[/b][hr]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.[/quote]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 [i]to[/i] 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.[/quote]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. :-DTue, 18 Sep 2012 17:08:26 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspxthe way i see it there is aprimary key if you split the header from the listsfor 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.Tue, 18 Sep 2012 13:39:39 GMTJayanth_Kuruphttp://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspxHallo 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'Tue, 18 Sep 2012 13:39:39 GMTrena24http://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspx[quote][b]dwain.c (9/18/2012)[/b][hr]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.[/quote]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 [i]to[/i] 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.Tue, 18 Sep 2012 13:31:30 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspxWow!!! 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!!! :-):-):-)Tue, 18 Sep 2012 02:19:05 GMTrena24http://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspxNow 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:[code="sql"]DECLARE @t TABLE (ItemID INT, strcol VARCHAR(100))INSERT INTO @tSELECT 222, 'H;123442;23%;78.99;52000990889'UNION ALL SELECT 222, 'L;socks;blue;100'UNION ALL SELECT 222, 'L;socks;red;200'UNION ALL SELECT 223, 'H;123443;23%;500.99;52000990111'UNION ALL SELECT 223, 'L;skirt;blue;100'UNION ALL SELECT 223, 'L;pants;red;200'[/code]Then reformat the delimited strings into something that can be CAST to XML datatype, and put this into a temporary table (or table variable):[code="sql"]DECLARE @x TABLE (ItemID INT, xmlcol XML);WITH CreateXML AS ( SELECT ItemID, strcol ,xmlcol=CASE SUBSTRING(strcol, 1, 1) WHEN 'H' THEN '<h>' + STUFF(deliveryPlace + '</deliveryPlace></h>', 1, 1, '') ELSE '<d>' + STUFF(qty + '</qty></d>', 1, 1, '') END ,rn=ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY (SELECT NULL)) FROM @t CROSS APPLY ( SELECT STUFF(strcol, CHARINDEX(';', strcol), 1, '<inv_number>')) a(inv_number) CROSS APPLY ( SELECT STUFF(inv_number, CHARINDEX(';', inv_number), 1, '</inv_number><vat>')) b(vat) CROSS APPLY ( SELECT STUFF(vat, CHARINDEX(';', vat), 1, '</vat><amount>')) c(amount) CROSS APPLY ( SELECT STUFF(amount, CHARINDEX(';', amount), 1, '</amount><deliveryPlace>')) d(deliveryPlace) CROSS APPLY ( SELECT STUFF(strcol, CHARINDEX(';', strcol), 1, '<itemDescription>')) e(itemDescription) CROSS APPLY ( SELECT STUFF(itemDescription, CHARINDEX(';', itemDescription), 1, '</itemDescription><color>')) f(color) CROSS APPLY ( SELECT STUFF(color, CHARINDEX(';', color), 1, '</color><qty>')) g(qty) ), CreateXML2 AS ( SELECT ItemID ,xmlcol=CAST(( SELECT CASE WHEN SUBSTRING(xmlcol, 2, 1) = 'h' THEN '<ItemID>' + CAST(ItemID AS VARCHAR(5)) + '</ItemID> ' ELSE '' END + xmlcol FROM CreateXML b WHERE a.ItemID = b.ItemID ORDER BY rn FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') AS XML) FROM CreateXML a GROUP BY ItemID )INSERT INTO @xSELECT ItemID, xmlcolFROM CreateXML2 [/code]I know, I know. It looks pretty ugly. But now getting at the data is relatively simple:[code="sql"]-- Extract headersSELECT x.row.value('(.)[1]', 'INT') AS ItemID ,x.row.value('(/h/inv_number)[1]', 'VARCHAR(50)') AS inv_number ,x.row.value('(/h/vat)[1]', 'VARCHAR(50)') AS vat ,x.row.value('(/h/amount)[1]', 'MONEY') AS amount ,x.row.value('(/h/deliveryPlace)[1]', 'VARCHAR(50)') AS deliveryPlaceFROM @xCROSS APPLY xmlcol.nodes('/ItemID') AS x(row)-- Extract detailsSELECT y.row.value('(.)[1]', 'VARCHAR(50)') AS ItemID ,x.row.value('(/h/inv_number)[1]', 'VARCHAR(50)') AS inv_number ,x.row.value('(./itemDescription)[1]', 'VARCHAR(50)') AS itemDescription ,x.row.value('(./color)[1]', 'VARCHAR(50)') AS color ,x.row.value('(./qty)[1]', 'INT') AS qtyFROM @xCROSS APPLY xmlcol.nodes('/d') AS x(row)CROSS APPLY xmlcol.nodes('/ItemID') AS y(row)[/code]:hehe: Jeez, Louise! What was I thinking! :w00t:I must have too much spare time on my hands.Tue, 18 Sep 2012 01:29:56 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspx[quote][b]rena24 (9/14/2012)[/b][hr]Ok, I am really thankful for your Help. My Company has created a ETL tool that we must use for import/Export Data. Sometimes I am allowed to use SSIS to create Excels or to read XML Data etc. Thank god! :-)Goodnight everyone (its Midnight here in Europe and I am so tired from work...:doze::doze:)and thanks again:-):-)[/quote]You are very welcome!!! Glad we could help...now go get some sleep!!!Fri, 14 Sep 2012 15:26:12 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspxOk, I am really thankful for your Help. My Company has created a ETL tool that we must use for import/Export Data. Sometimes I am allowed to use SSIS to create Excels or to read XML Data etc. Thank god! :-)Goodnight everyone (its Midnight here in Europe and I am so tired from work...:doze::doze:)and thanks again:-):-)Fri, 14 Sep 2012 15:24:36 GMTrena24http://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspx[quote]The thing is that I am not allowed to use SSIS. Just Sql and executables are allowed[/quote]Sheesh!!! Some companies have the strangest rules/regulations about things. There is a tool that you already own that is designed to do this complicated task that you are trying to deal with but the politics get in the way.It seems you are well on your way of making the best of a painful situation. Post back if you have any issues.Fri, 14 Sep 2012 15:11:33 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspxThank you for the Help. The thing is that I am not allowed to use SSIS. Just Sql and executables are allowed... I know thats really silly but what can I do? I could call the SSIS through the sql command but I would probably get fired the next day ;-);-);-). But you have give me a good Idea to improve the simple vb.code that I had wrote. I will everytime when I find the Header Identifier create a new Guid instead to increase my integer!!! Thanks, thats a first step to improve the whole thing!!:-):-):-)[code="vb"]Dim strFileName() As StringstrFileName = IO.File.ReadAllLines(filename, Encoding.GetEncoding(1253))Dim headerIdent as string = "H" Dim g As Guid For Each myLine In strFileName If Not myLine = Chr(endCharacter) Then If myLine.Substring(0, 2) = headerIdent Then g = Guid.NewGuid() End If tempStr &= g & ";" & myLine.ToString & Environment.NewLine End If Next[/code]Fri, 14 Sep 2012 14:54:27 GMTrena24http://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspx[quote][b]rena24 (9/14/2012)[/b][hr]Yes, I read the Data from a file! So, before I insert my Data in a Table I run the vb.code above and I recreate the file with the generated ref_number. The raw Data in the file are (semilicon (;) delimited)-->H;123442;23%;78.99;52000990889L;socks;blue;100L;socks;L;red;200H;123443;23%;500.99;52000990111L;skirt;blue;100L;pants;red;200Thank you!![/quote]With that data, you have another reason you're going to need a transformation script component, you'll need to split the streams and manually break out the data. OW. Diff # of columns really can mess you up.Alright, here's the short form. Create a flat file source that has *1* column, and ends on CRLF (or whatever ends the tail of each row). This feeds into your transformation script component. That script component will have two outputs, one for headers and one for line items. These you'll configure as expected for the correct columns, data types, etc.In your code, you'll have to detect which output that row belongs to and 'ship it' to the correct output. This gets uglier and uglier, I'd hoped you'd be able to use generic column builds. The alternative is to use completely generic columns of varchar() with ragged right on the data source and feed them into staging tables, and then deal with all your validation and the like after they're in the SQL staging tables.Fri, 14 Sep 2012 14:49:38 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspxYes, I read the Data from a file! So, before I insert my Data in a Table I run the vb.code above and I recreate the file with the generated ref_number. The raw Data in the file are (semilicon (;) delimited)-->H;123442;23%;78.99;52000990889L;socks;blue;100L;socks;L;red;200H;123443;23%;500.99;52000990111L;skirt;blue;100L;pants;red;200Thank you!!Fri, 14 Sep 2012 14:43:25 GMTrena24http://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspxAh, the old mainframe data sort issue. I HATE THEM. However, the solution isn't rediculously hard... as long as you're not loaded to SQL yet.This takes a bit of chicanery mostly because the system styles are so different. As you've already mentioned, you'd like to get this into a table hierarchy. The problem is the pre association.The solution to this is during the load sequence, as mentioned above. This is a place where SSIS can shine compared to some of the more traditional T-SQL methods. Using a synchronous transformation script component, you can read the records through in the order from the file and assign a new column an identifier (GUIDs in this case are probably easiest for first pass, though an int can be easily used to staging tables as well) . What you'll do is detect each row as it comes through, and when the row type is Header you'll change the identifier and assign it to each row that comes through until you hit the next header. This sounds more complicated than it really is, but you need a little (very little, I can do it) familiarity with VB or C# Coding.If the data is already table loaded, this gets uglier, mostly because you've probably lost any chance of knowing what data belongs where. Hopefully you still have the original load files. If not, you're in for a headache. Script the data out during table scripting (it's an option in SSMS) and dump that to a new query page. Now, manually (because you have to check the actual data against the original ANYWAY) add in your identifier column and corresponding values to the 'everything in one' table. This will give you a new staging structure that will allow you to feed your two proper tables. Once that's done you should be able to modify your import process to never have to do that again.Fri, 14 Sep 2012 14:41:19 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspxAre you reading this data in from a file? If so, then this is not a big deal at all. The biggest challenge is that if this data is already in SQL there is nothing use as an order by. If it is however in a file then the file IS the order. Will be interested to see what you post. I won't be on much, if at all, over the weekend.Fri, 14 Sep 2012 14:32:52 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspxOk, and thanks again for the answer. Its my third post and I was really in a hurry.. Sorry!! I will tomorrow rewrite the post in the right format. I know that the Data is dificcult and the only solution that I had was the vb code above. It works fine but I want and I know that there is a really better way to do that.Goodnight everyoneand thank you!!:-):-)Fri, 14 Sep 2012 14:12:01 GMTrena24http://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspxSure I can help. But first you need to help me. See the first link in my signature about best practices when posting questions. Until you post stuff we can work we are at best shooting in the dark. Post up some ddl and sample according to that article and we can help.The real challenge is that the way your data is it is going to be very difficult to get this right because you have nothing that ties the details to the header at the moment. Once we have full ddl it will be easier to determine how to proceed.Fri, 14 Sep 2012 13:59:40 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspxHello and thanks for your fast Answers and that you try to Help me!!!The thing is that I really dont have a primary key or refernece Number and that way I cant normalize my Data.I give you a better example:inv_number;vat;amout;deliveryPlaceH;123442;23%;78.99;52000990889itemDescription;color;qtyL;socks;blue;100L;socks;red;200So my raw data comes in that way -->H;123442;23%;78.99;52000990889L;socks;blue;100L;socks;L;red;200H;123443;23%;500.99;52000990111L;skirt;blue;100L;pants;red;200I want to split my Data and \insert them in two tables. A header table and an item table. The Problem is that I dont have a reference key (not primary.. Sorry!!) and I must first generate one. The only thing that I have is,that the H letter in the First column shows that this raw is the header and the L means thats the Line. The Data comes subquential. That means that the inv_number 123442 has the two items that follow (socks) and the other one has the skirt and pants.That means I must somehow generate the reference key that shows the relation between the invoice and the items. Its then the primary key for the Header Table and the Foreign Key for the item table. Example:222;H;123442;23%;78.99;52000990889222;L;socks;blue;100222;L;socks;red;200223;H;123443;23%;500.99;52000990111223,L;skirt;blue;100223,L;pants;red;200I already have write a simple code in VB. It reads the file in a array string and I generate the ref number in that way -But I am sure there is a better way to do that only with sql. Please help!!! I hope my code here helps to explain the logik.->------------------------[code="vb"]Dim strFileName() As StringstrFileName = IO.File.ReadAllLines(filename, Encoding.GetEncoding(1253))Dim headerIdent as string = "H" Dim Num as Integer = 222 For Each myLine In strFileName If Not myLine = Chr(endCharacter) Then If myLine.Substring(0, 2) = headerIdent Then num = num + 1 End If tempStr &= num.ToString.PadLeft((idLen), "0"c) & myLine.ToString & Environment.NewLine End If Next[/code]---------------------------------------Thank you!!!!:-):-):-)Fri, 14 Sep 2012 13:55:30 GMTrena24http://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspxThere is no default sort order, so SELECT * FROM Table is not guaranteed to give the same sequence you put the records into the table.The column you added cannot be a primary key because it is not unique.The ROW_NUMBER() function needs an ORDER BY clause - which column is used to order the data? - there does not appear to be one from the data you gave.From the data given, I don't think it can be done. I would need more information, like the full table definition, and where the data comes from (an Excel file? it appears to be CSV)Fri, 14 Sep 2012 13:11:30 GMTTom Brownhttp://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspx[quote][b]rena24 (9/14/2012)[/b][hr]Hello everyone!!!I have a file with these Data -->H;2334;8989;90;000L;XXX;YYY;90;000L;UUU;PPP;78;000H;4445;855989;90;000L;AAA;BBB;90;000L;YYY;TTT;78;000I want to set a primary Key (gid) to each row in that way -->1;H;2334;8989;90;0001;L;XXX;YYY;90;0001;L;UUU;PPP;78;0002;H;4445;855989;90;0002;L;AAA;BBB;90;0002;L;YYY;TTT;78;000Is there any way to do this with row number or with an other newer sql command? I dont want to use a cursor (I hate it). Thanks in advance!!!!Greetings Rena[/quote]Glad to hear you don't want to use a cursor for a simple update. However, what is your primary key? In your example it looks like you added a column to the beginning with a value that repeats itself 3 times. If you are asking if you can have a primary key column with multiple rows containing the same value I suggest you read about what a primary key is...If you can explain clearly what you are trying to do we can help. It would probably be best if you post ddl(create table scripts).Fri, 14 Sep 2012 13:02:54 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1359470-1292-1.aspxHello everyone!!!I have a file with these Data -->H;2334;8989;90;000L;XXX;YYY;90;000L;UUU;PPP;78;000H;4445;855989;90;000L;AAA;BBB;90;000L;YYY;TTT;78;000I want to set a primary Key (gid) to each row in that way -->1;H;2334;8989;90;0001;L;XXX;YYY;90;0001;L;UUU;PPP;78;0002;H;4445;855989;90;0002;L;AAA;BBB;90;0002;L;YYY;TTT;78;000Is there any way to do this with row number or with an other newer sql command? I dont want to use a cursor (I hate it). Thanks in advance!!!!Greetings RenaFri, 14 Sep 2012 10:00:34 GMTrena24