Update cursor or something else? 1

[briangriffin]

I have a daily list of charges that I'm pumping into SQL Server 2008, however that list comes as a text file and I can't change the format.

The problem is that the department is only listed for the first record; all subsequent records with a null department belong to the previous department value. The table looks like this:

RecordNum     Batch     Dept     ...more fields
1              1000      PH
2              1000
3              1001
4              1000      FA
5              1001
6              1002      IT

So I really need to backfill the table, setting the department to whatever came before it - in the example recordnums 2 and 3 would have the dept set to PH, record 5 would be set to FA, etc.

This is a case where an update cursor would seem to be appropriate, and the recordsets are small enough that performance would not be an issue, but first I'm wondering if there is another way. Recordnum is an identity column.

Thanks as always for the input.

 

[markros]

Take a moment to look up the solution to this problem listed in this blog post

http://beyondrelational.com/blogs/naomi/archive/2010/10/17/interesting-t-sql-problems.aspx

Last problem is your problem.

PluralSight Learning Library

 

[briangriffin]

Well that one goes in my 'Handy Code' file - thanks!