Replace Cursor Next number assignment Operation with Set based equivalent

Question

Good day all,

I have the following cursor query and would like to replace it with a set based query to address performance issues.

DECLARE @EmpIDM CHAR(21);
DECLARE @EmpName CHAR(21);
DECLARE @EMPCatID INT;

DECLARE Assign_Emp SCROLL CURSOR
FOR
SELECT DISTINCT EMP
    , EMPNAME
FROM HR_EMPLOYEES

SET NOCOUNT ON

OPEN Assign_Emp;

FETCH NEXT
FROM Assign_Emp
INTO @EmpIDM
    , @EmpName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @EMPCatID = (
            SELECT TOP 1 CategoryID
            FROM Categories
            )

    UPDATE Categories
    SET CategoryID = (CategoryID + 1) /*Increment Category ID for next Insert*/

    INSERT INTO Table1 (
        EmpNumber
        , EmployeeName
        , EmployeeCategoryID
        )
    VALUES (
        @EmpIDM
        , @EmpName
        , @EMPCatID
        )

    FETCH NEXT
    FROM Assign_Emp
    INTO @EmpIDM
        , @EmpName
END

CLOSE Assign_Emp;

CLOSE Assign_Emp;

SET NOCOUNT OFF

My challenge is adapting the following code segment into a set based operation

SET @EMPCatID = (
            SELECT TOP 1 CategoryID
            FROM Categories
            )

    UPDATE Categories
    SET CategoryID = (CategoryID + 1) /*Increment Category ID for next Insert*/

I humbly appreciate any insight on how I can achieve this.

Many Thanks,

Answer 1

Re-write using temp. table with identity column:

declare @offset int

select @offset = isnull(max(CategoryID),0) from Categories

create table #data (
  EmpNumber CHAR(21),
  EmployeeName CHAR(21),
  EmployeeCategoryID int identity
)

INSERT INTO #data (
        EmpNumber
        , EmployeeName)
SELECT DISTINCT EmpIDM
    , EmpName
FROM HR_EMPLOYEES

insert into Table1 (
        EmpNumber
        , EmployeeName
        , EmployeeCategoryID
) select
        EmpNumber
        , EmployeeName
        , EmployeeCategoryID + @offset
from #data

update Categories 
set CategoryID = (select max(EmployeeCategoryID) from #data) + @offset