I hope you can help. I have an SQL table (imported from another data source) that has a group of rows that make up one record. However, the record ID is only in the "Segment" labeled MSH, in col010 of the row.
What I would like to do is to get that value and assign it to the col010 field in subsequent rows until Segment = MSH again. Then get that new number and assign it to the col010 fields, etc.
The records are brought in to the SQL table in sequence.
I wouldn't doubt that for an instant. The problem is that just because the rows came into SQL Server "in sequence" doesn't mean diddly about being able to get them back out "in sequence" unless you do something like adding a column with either sequence or time of insert information.
If you have a lot of faith and don't mind the occasional sequencing disaster, you can rely on some tricks that work a lot more often than they don't work, but there is no guarantee that it will work on any given run and a virtual certainty that it will break now and then.
This isn't good enough for most folks. Can you live with it?