Avoiding Cursors with Superclass-Subclass Tables

Ever since I read Itzik Ben-Gan's article in SQL Server Magazine about changing cursor-based logic to set-based logic, I've avidly sought to avoiding using cursors in my SQL procedures. One of the biggest problems that I've faced on this front is the issue of inserting records into a superclass-subclass table structure. Here's an example of the table structure that I'm talking about:


I spent hours trying to think of a solution to this problem that didn't use cursors, but all I could ever come up with is something like the following:

// Give every employee a bonus!
DECLARE @curEmployees CURSOR
SET @curEmployees = CURSOR FOR SELECT EmployeeID, BonusAmount FROM tblEmployee

DECLARE @employeeID int
DECLARE @bonusAmount money

OPEN @curEmployees
FETCH NEXT FROM @curEmployees INTO @employeeID, @bonusAmount
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @transactionID int

INSERT INTO tblTransaction (TransactionClass, [Date], Amount)
VALUES ('PR', '11/21/2005', @bonusAmount)

SET @transactionID = @@IDENTITY

INSERT INTO tblPayrollTransaction (TransactionID, TransactionClass, EmployeeID)
VALUES (@transactionID, 'PR', @employeeID)

FETCH NEXT FROM @curEmployees INTO @employeeID, @bonusAmount

END

CLOSE @curEmployees

After writing blocks of code like this about twenty times, I finally thought up a solution that avoids cursors. To be honest, I haven't done exhaustive performance testing on the solution or anything, but I did notice performance improvements with my new method. Check it out:

// Give every employee a bonus more quickly!
DECLARE @tblEmployee TABLE
(

EmployeeID int PRIMARY KEY,
BonusAmount money,
RowNum int IDENTITY(0, -1)

)

INSERT INTO @tblEmployee (EmployeeID, BonusAmount)
SELECT EmployeeID, BonusAmount FROM tblEmployee

INSERT INTO tblTransaction (TransactionClass, [Date], Amount)
SELECT 'PR', '11/21/2005', BonusAmount FROM @tblEmployee ORDER BY RowNum

INSERT INTO tblPayrollTransaction (TransactionID, TransactionClass, EmployeeID)
SELECT @@IDENTITY + RowNum, 'PR', EmployeeID FROM @tblEmployee ORDER BY RowNum

The critical elements of the new code are as follows:

1. The table variable must contain the most granular instance of an item to be inserted. In this example, each employee has a single transaction, so employees are stored in the table variable.
   
2. The table variable must have an identity column that starts at zero and counts backwards. (The assumption here is the TransactionID on tblTransaction is an IDENTITY(1,1) column.)
   
3. Both insert statements must have identical ORDER BY clauses that include the RowNum column.

If all of these assumptions hold true, then the two insert statements should "line up" and cause each superclass record to be properly joined to the corresponding subclass record.