Sql table missing data HELP!
Posted on 2005-04-06
One of my sql tables is doing something strange and I need help cleaning it up. This would be an easy fix in excel if the whole table would fit! This entire table has somewhere around 1.4 million rows I think. Here's what the data looks like:
SRC Date AcctNum RefNum Amt
9 4-3-05 1001 & 500
9 4-3-05 1001 & -500
9 4-3-05 1001 & 100
9 4-3-05 1001 12345 300
So the entire table goes in blocks like this. In excel to fix this (usually the table is very small because I only import current month) I would simply reverse the tables order so the correct refnum is first, then I would write a simple formula to copy the refnum down and overwrite the "&"'s until it hits a new refnum.
Now due to the table size I can't clean this data up in excel. So I'm kinda lost in ms sql on cleaning this. I don't even know how to import it into sql and reverse the table order so the correct refnum starts first.
Can this be done with a stored procedure? I've been looking at the sql commands and I can't seem to find anything on filling down based on some constraints. The thing is that the src and date will always be the same per "group" of data. This table is just a journal of all accounting postings. So the refnum should be the same but the system that uses this data knows how to copy down the refnum for each line.
I'm lost at this point and I have to get this cleaned up as I can't link any of my reports based on refnum.