5

I have a spreadsheet I'm importing into the database raw, a SQL Server 2016 database. It has records in what is known as the "Lockbox format", used by banks apparently. It looks something like this generically:

Batch1 details
child record
another child record
summary for batch
Header Row for Batch2

I have a fiddle set up here.

What's important to note is:

  • Column 1 indicates the "type" of record the row is. It's the only "universal" column.
  • Every other column changes in nature based on the type of record.
  • The only thing tying child records to a parent is that they appear after a parent and before the next parent. ID's are not shared between rows.
  • There are a variable number of child rows following a batch (not predictable).

Is there a way to query this so that I can easily group e.g. a batch and it's child + footer records? One of the things I have to do is indicate that the footer records contain the right aggregates (taken from the child records; including the # of child records for a batch, which is part of what I need to verify).

  • please add a 'row number' column to your fiddle, so it will be possible to find a header for each row. – Piotr Oct 16 at 17:33
  • 2
    Multirecord formats are a common thing for mainframes and such and are a painful concept for ETL tools like SSIS and even worse for TSQL as the definition of the row is entirely dependent on either the preceding row or the current row. Generally speaking, your best approach is to use a row based processing approach, like C# or your favorite language, to load this data in and perform validation. – billinkc Oct 16 at 17:38
  • @piotr - added that column – peacedog Oct 16 at 17:39
  • @billinkc I was sort of afraid this would turn out to be the case, but I was asked to do this in sql (also, when this was begun I was lead to believe the rows were directly linked, only now it turns out they are not). I'll kick it back up the chain of command if need be. – peacedog Oct 16 at 17:40
  • The simplest method to import the data into SQL Server in such a way that you can process them in SQL Server is to import it into a pre-defined table with an IDENTITY column that will be automatically filled. Then a query like `SELECT MAX(RowID) FROM Table WHERE RowID <= Table.RowID AND Column1 = 1) will get the header ID for all rows, including the header rows, and all rows will be groupable on that value. – Laughing Vergil Oct 16 at 17:49
1

Assuming the ordering of your input data is such that the beginning of the batch (Column1=1) always has the least RowNumber within that batch, you can do something like this to assign pseudo batch numbers using the largest RowNumber among the preceding header records:

select t.*, 
    max(case column1 when 1 then RowNumber end) 
    over(order by rownumber range between unbounded preceding and current row) as batchid
from tbl t  

(fiddle)

From there you can use aggregate or window functions to calculate aggregates if you wrap the above in a subselect and use the pseudo batch numbers to partition data or group data:

select 
max(case column1 when 1 then column3 end) as batchid,
sum(case column1 when 2 then some_numeric_column end) as sum_children,
max(case column1 when 8 then column3 end) as footer_value
from (
  select t.*, 
    max(case column1 when 1 then rownumber end) 
    over(order by rownumber range between unbounded preceding and current row) as batchnum
from tbl
) t1
group by batchnum
  • This definitely answered the question although I went in another direction (I did a little extra work in the api to make sure every record had a batch assigned to it). – peacedog Oct 22 at 14:25
1

The meaning of records within the file is inherently depending on the sequence of the records. In a relational database, however, no sequence of rows in a table is guaranteed. Therefore as soon as you load the file into a table you have lost a vital piece of information.

Further, rows within a table are required to have the same columns. Since each record in the file can have different fields the two are inimicable.

You can preserve order by using OPENROWSET with the BULK option. While the documentation does not explicitly state that source order is preserved it allows FIRSTROW and LASTROW to be specified so the implication is strong. This will allow the injection of an identity or sequence into the data, or a cursor, so source order can be reproduced in later processing.

You can work work around the second restriction by subverting good table design somewhat and treating columns as BLOBs whose meanings are defined at run-time. This will inevitably lead to defining those columns at character types and subsequent string manipulation in T-SQL, which is rarely pretty.

A better approach would be to pre-process the file in a programming language and write to SQL Server only once the batches have been correctly interpreted. Should the PHB insist on a T-SQL solution consider SQL CLR.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.