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

  • Hello everyone!!!

    I have a file with these Data -->

    H;2334;8989;90;000

    L;XXX;YYY;90;000

    L;UUU;PPP;78;000

    H;4445;855989;90;000

    L;AAA;BBB;90;000

    L;YYY;TTT;78;000

    I want to set a primary Key (gid) to each row in that way -->

    1;H;2334;8989;90;000

    1;L;XXX;YYY;90;000

    1;L;UUU;PPP;78;000

    2;H;4445;855989;90;000

    2;L;AAA;BBB;90;000

    2;L;YYY;TTT;78;000

    Is 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

  • rena24 (9/14/2012)


    Hello everyone!!!

    I have a file with these Data -->

    H;2334;8989;90;000

    L;XXX;YYY;90;000

    L;UUU;PPP;78;000

    H;4445;855989;90;000

    L;AAA;BBB;90;000

    L;YYY;TTT;78;000

    I want to set a primary Key (gid) to each row in that way -->

    1;H;2334;8989;90;000

    1;L;XXX;YYY;90;000

    1;L;UUU;PPP;78;000

    2;H;4445;855989;90;000

    2;L;AAA;BBB;90;000

    2;L;YYY;TTT;78;000

    Is 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

    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).

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • There 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)

  • Hello 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;deliveryPlace

    H;123442;23%;78.99;52000990889

    itemDescription;color;qty

    L;socks;blue;100

    L;socks;red;200

    So my raw data comes in that way -->

    H;123442;23%;78.99;52000990889

    L;socks;blue;100

    L;socks;L;red;200

    H;123443;23%;500.99;52000990111

    L;skirt;blue;100

    L;pants;red;200

    I 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;52000990889

    222;L;socks;blue;100

    222;L;socks;red;200

    223;H;123443;23%;500.99;52000990111

    223,L;skirt;blue;100

    223,L;pants;red;200

    I 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.->

    ------------------------

    Dim strFileName() As String

    strFileName = 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

    ---------------------------------------

    Thank you!!!!:-):-):-)

  • Sure 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.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Ok, 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 everyone

    and thank you!!:-):-)

  • Are 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.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Ah, 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.


    - 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

  • 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;52000990889

    L;socks;blue;100

    L;socks;L;red;200

    H;123443;23%;500.99;52000990111

    L;skirt;blue;100

    L;pants;red;200

    Thank you!!

  • rena24 (9/14/2012)


    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;52000990889

    L;socks;blue;100

    L;socks;L;red;200

    H;123443;23%;500.99;52000990111

    L;skirt;blue;100

    L;pants;red;200

    Thank you!!

    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.


    - 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

  • Thank 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!!:-):-):-)

    Dim strFileName() As String

    strFileName = 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

  • The thing is that I am not allowed to use SSIS. Just Sql and executables are allowed

    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.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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:-):-)

  • rena24 (9/14/2012)


    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:-):-)

    You are very welcome!!! Glad we could help...now go get some sleep!!!

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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:

    DECLARE @t TABLE (ItemID INT, strcol VARCHAR(100))

    INSERT INTO @t

    SELECT 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'

    Then reformat the delimited strings into something that can be CAST to XML datatype, and put this into a temporary table (or table variable):

    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 @x

    SELECT ItemID, xmlcol

    FROM CreateXML2

    I know, I know. It looks pretty ugly. But now getting at the data is relatively simple:

    -- Extract headers

    SELECT 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 deliveryPlace

    FROM @x

    CROSS APPLY xmlcol.nodes('/ItemID') AS x(row)

    -- Extract details

    SELECT 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 qty

    FROM @x

    CROSS APPLY xmlcol.nodes('/d') AS x(row)

    CROSS APPLY xmlcol.nodes('/ItemID') AS y(row)

    :hehe: Jeez, Louise! What was I thinking! :w00t:

    I must have too much spare time on my hands.


    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

Viewing 15 posts - 1 through 15 (of 19 total)

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