How would one write a set based query to replace a cursor to handle this situation?
Posted on 2013-12-17
Given a flat file that looks like this:
AcctNum Date1 Value1 Date2 Value2 Date3 Value3 ...... through Value 13
This is a rolling data file supplied by a customer for the last 13 months of data. For example: in the August file Date1 would probably be something like 20130811, Date2 would be the previous month, 20130705, etc.
When we would get the September file, Date1 would be 20130915, Date2, 20130811, Date3 20130705. See the pattern?
What I am trying to do is create a small subset of Value records for each account/date. Example : AcctNum: 0001, TransDate 201308, Value 5555 AcctNum: 0001, TransDate 201309, Value 6666
I've been able to do this with a cursor, going through each Date/Value pair from the input file (13 times per record), determining if the AcctNum/TransDate already exists in our subset, if not, insert it.
Needless to say, this is extremely slow and we have at least 750K records per month to process. Is there any way to handle this without using a cursor?
I'm trying to be as concise as possible, but let me know if I can clarify anything.