November 30, 2009 at 7:29 am
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
November 30, 2009 at 8:58 am
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.
November 30, 2009 at 9:59 am
Thank you Mr.Steve.
But we don't receive the data in any particular order. Can this operation be done in SSIS?
November 30, 2009 at 10:29 am
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.
December 7, 2009 at 4:13 pm
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