I have a fairly complex query which insert multiple rows into a table from multiple tables. Its works fine but I really don't want to use cursor as some blog says cursor took long time for execution (of course it depends). I hope I could find alternative way for minimize my execution time or cursor complexity. Here is my query:
DECLARE @employeeid int;
DECLARE @joiningdate date;
DECLARE @quitdate date;
DECLARE @eodrefs int,@amounts decimal;
select @employeeid=HRMS_Employee.ID,@joiningdate=HRMS_Employee.Joining_Date,@quitdate=HRMS_Employee.QuitDate
from HRMS_Employee where HRMS_Employee.Present_Status=1 and ID=4
DECLARE @tabEodRecord table(eodref int,amount decimal)
insert INTO @tabEodRecord (eodref,amount)
select HRMS_EodRecord.Eod_RefFk,HRMS_EodRecord.ActualAmount from HRMS_EodRecord join HRMS_Employee on HRMS_EodRecord.EmployeeId=HRMS_Employee.ID
where HRMS_EodRecord.Status=1 and HRMS_EodRecord.EmployeeId=@employeeid
IF CURSOR_STATUS('global','PayrollInfoCursor')>=-1
BEGIN
DEALLOCATE PayrollInfoCursor
END
DECLARE PayrollInfoCursor CURSOR
FOR (select eodref,amount from @tabEodRecord)
OPEN PayrollInfoCursor;
FETCH NEXT FROM PayrollInfoCursor INTO @eodrefs,@amounts
WHILE @@FETCH_STATUS = 0
BEGIN
insert HRMS_PayrollInfo (PayrollFK,EmployeeIdFk,Eod_ReferenceFk,Amount,PaymentDate,[Status],AlertActive,Entry_By,Entry_Date,Update_By,Update_Date)
select 26,@employeeid,@eodrefs,@amounts,getdate(),1,1,1,getdate(),1,getdate()
FETCH NEXT FROM PayrollInfoCursor INTO @eodrefs,@amounts
END
CLOSE PayrollInfoCursor
DEALLOCATE PayrollInfoCursor
I hope there is something like this (however this won't work) because subquery returns multiple rows
insert HRMS_PayrollInfo (PayrollFK,EmployeeIdFk,Eod_ReferenceFk,Amount,PaymentDate,[Status],AlertActive,Entry_By,Entry_Date,Update_By,Update_Date)
select 26,@employeeid,(select eodref from tabEodRecord),(select amountfrom tabEodRecord),getdate(),1,1,1,getdate(),1,getdate()