SQL Loop through records in a table

Question

I have a table called master, the primary key is account_num. Each account number has an account_type (single character). I need to do the following:

1. find all accounts with a type of A or B.
2. store that account number in a new table called year_end_close along with a time stamp of when that transaction takes place
3. set all accounts in master with a type of A to C, and all accounts with a type of B to D

What's the best way to handle this in SQL? While loop? Case statement? Cursor? Any help is appreciated. The table has about 17,000 rows.

Answer 1

You shouldn't need to use a cursor/loop to do something like this. When writing SQL, always try to look for a set-based solution first. I would recommend a CASE statement, which was one of the options you mentioned.

Try this:

BEGIN TRAN;

SELECT account_num, CURRENT_TIMESTAMP
INTO year_end_close
FROM dbo.master
WHERE account_type IN ('a','b');

UPDATE dbo.master
SET account_type = CASE account_type
                     WHEN 'a' THEN 'c'
                     WHEN 'b' THEN 'd'
                     ELSE account_type
                     END
WHERE account_type IN ('a','b');

COMMIT TRAN;