1

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,

Nqabeni Simela
  • 77
  • 1
  • 1
  • 10

1 Answers1

-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
James Z
  • 12,209
  • 10
  • 24
  • 44
  • Oh sorry, didn't realize you were using that... How about using a temp table with identity column? – James Z Feb 08 '15 at 20:03
  • This might just work.Thanks,this was proving to be a headache for me. @Vladimir your insight is quite useful. Thank you so much for your responses.Much appreciated.Many Thanks. – Nqabeni Simela Feb 09 '15 at 06:18