Row based update

  • Hi all,

    We have data that looks like the following example, We receive data in different batches in same text file like the following example where there should be only one header and trailer but it has multiple header and trailer.

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

    COL1 COL2

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

    header000001

    abc 000001

    bcd 000002

    cde 000003

    def 000004

    trailer 000001

    header000002

    abc 000001

    bcd 000002

    cde 000003

    def 000004

    trailer 000002

    header000003

    abc 000001

    bcd 000002

    cde 000003

    def 000004

    trailer 000003

    I would like to update another column which give me the hearder information, the following describes how the result should look like.

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

    COL1 COL2 COL3

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

    header000001 000001

    abc 000001 000001

    bcd 000002 000001

    cde 000003 000001

    def 000004 000001

    trailer 000001 000001

    header000002 000002

    abc 000001 000002

    bcd 000002 000002

    cde 000003 000002

    def 000004 000002

    trailer 000002 000002

    header000003 000003

    abc 000001 000003

    bcd 000002 000003

    cde 000003 000003

    def 000004 000003

    trailer 000003 000003

    Can someone please help me in writing an update query or script for such data?

    Thank you,

    Ashu

  • If you are working on position, you would have to have some column that lets you now what order is where. Once data is in SQL Server, it has no order, regardless of how you inserted it. Positions are meaningless without some column to ORDER BY.

    Once you have that, you can find the header value prior to the current one with T-SQL.

  • Thank you Mr.Steve.

    But we don't receive the data in any particular order. Can this operation be done in SSIS?

  • I would guess it could, but you'd end up processing row by row. If you don't get the data in any order, how do you know which items are linked as 1 and which are 2? You're taking them in natural order, which is a valid way of processing things, but without adding some distinguishing field in SQL Server, like an Identity, you can't easily determine which rows are batched with the other ones.

  • My guess is that you are getting the detail records right after the header record they belong to. If not, the data would make no sense no matter what you do.

    Assuming the above. You could insert into a table with an identity value, that way you should be able to preserve the insert order.

    How to process it from there..... I would guess (hides from Grant et.all) a loop would almost be required for this, potentially create a seperate table with the headers and keeping the Identity values, then using some algorithm to join between the 2?

Viewing 5 posts - 1 through 4 (of 4 total)

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