I'm looking for following kind of feedback:
- Firstly, is the use of a cursor an overkill here?
- Is there a simpler way to do what I'm doing in SQL?
- Could the script go terribly wrong?
- I have limited SQL experience, how does it look? Any standards that I'm not following?
Bear in mind that this is a one-time script to be run, so performance is not absolutely crucial. Nevertheless, I would like to have some feedback on whether it could still be done more efficiently.
A few words about what I'm trying to do: There are two tables,
tblUserCodeList. The first table (
tblEmployment) has a column of type int named
RepaymentType. Due to a requirements change, this has to be abandoned and converted to a specific GUID/uniqueidentifier to be stored in another column
RepaymentTypeIdentifier in the same
tblEmployment. That is the whole point of the SQL script. I'll leave out further business logic.
So for each row in
tblEmployment I want to read its
RepaymentType and depending on its value (can only be 1-12) I want to store a specific GUID from
UserCodelistIdentifier) in the column
RepaymentTypeIdentifier is thus a Foreign Key referencing
(I have shortened the CASE statement a little bit.)
USE db_name_left_out... GO DECLARE @EmploymentId as NVARCHAR(50); DECLARE @UserCodeList TABLE ( Title NVARCHAR(100), UserCodeListIdentifier NVARCHAR(50) ) INSERT INTO @UserCodeList (Title, UserCodeListIdentifier) SELECT Title, UserCodeListIdentifier FROM tblUserCodeList; Declare @EmpCursor as CURSOR SET @EmpCursor = CURSOR FOR SELECT EmploymentIdentifier FROM dbo.tblEmployment; OPEN @EmpCursor; FETCH NEXT FROM @EmpCursor INTO @EmploymentId; WHILE @@FETCH_STATUS = 0 BEGIN UPDATE dbo.tblEmployment SET RepaymentTypeIdentifier = CASE when RepaymentType = 1 then (SELECT TOP 1 UserCodeListIdentifier FROM @UserCodeList WHERE title like '%some_text_1%') when RepaymentType = 2 then (SELECT TOP 1 UserCodeListIdentifier FROM @UserCodeList WHERE title like '%some_text_2%') when RepaymentType = 3 then (SELECT TOP 1 UserCodeListIdentifier FROM @UserCodeList WHERE title like '%some_text_3%') . . . when RepaymentType = 12 then (SELECT TOP 1 UserCodeListIdentifier FROM @UserCodeList WHERE title like '%some_text_12%') END WHERE EmploymentIdentifier = @EmploymentId FETCH NEXT FROM @EmpCursor INTO @EmploymentId; END CLOSE @EmpCursor DEALLOCATE @EmpCursor;