SQL 2005: Optimize upsert-like Stored Procedure using Cursor, possible?

Question

Ok So this is the second time i'm trying to fix this.

I was wondering if there is a possible way to optimize the cursor created for a stored procedure, used to iterate through a big select statement with two unions. In which later on, the stored procedure begins inserting values to a staging table checking each value against a "where not exist" select statement.

Or better yet, is it possible to create all this with a select statement and possibly joins.

The inserting process takes far too long to complete, and I would recon selecting the data would be much faster.

Here is an example of the SQL:

declare @ID1 varchar(40) ,
        @ID2 varchar(20) ,
        @State varchar(20) ,
        @isActive bit

Declare CuTable SCROLL INSENSITIVE cursor for
    Select 
        Cast(ID1 as Varchar(20)) AS ID1, 
        Cast(ID2 as Varchar(20)) AS ID2,  
        'AT' AS [State], 
        CASE When (isAvtiveDate > { fn CURDATE() }) or isAvtiveDate is null Then 1 else 0 end AS isAvtive
    From 
        server1.db.dbo.table1 
    Inner Join 
        server1.db.dbo.table2 on ID2 = ID1
    Where ID3 = 1 AND isActiveDate <= ISNULL(isActiveDate,'2020-01-01')

    UNION

    Select
        Cast(ID1 as Varchar(20)) AS ID1, 
        Cast(ID2 as Varchar(20)) AS ID2, 
        'AP' AS [State], 
        CASE When (isActiveDate > { fn CURDATE() }) or isActiveDate is null Then 1 else 0 end AS isActive
    From 
        server1.db.dbo.table1 
    Inner Join 
        server1.db.dbo.table2 on ID2 = ID1
    Where 
        ID3 = 2 AND isActiveDate <= ISNULL(isActiveDate,'2020-01-01')
UNION
Select
           Cast(ID1 as Varchar(20)) AS ID1, 
           Cast(ID2 as Varchar(20)) AS ID2,
           'AH' AS [State], 
           CASE When (isActiveDate > { fn CURDATE() }) or isActiveDate is null Then 1 else 0 end AS isActive
From server1.db.dbo.table1 inner join server1.db.dbo.table2 on ID2 = ID1
           inner join server1.db.dbo.table13 on ID2 = ID4
Where ID3 = 5 and toDate is null and fromDate is not null AND isActiveDate <= ISNULL(isActiveDate,'2020-01-01')


Open CuTable
Fetch Next From CuTable Into  @ID1, @ID2, @[State], @isActive

While @@Fetch_Status = 0

Begin
    Insert Into StagingTable (ID1, ID2, [State], isActive) 

           --Values 
           Select @ID1, @ID2, @[State], @isActive
           where not exists(select * from StagingTable  where ID1 = @ID1 and ID2 = @ID2)


Fetch Next From CuTable Into @ID1, @ID2, @[State], @isActive

End

close CuTable
deallocate CuTable

HEADS UP: I'm using SQL SERVER 2005

UPDATE regarding Leonidas199x comment thread:

Answer 1

Don't think you need a cursor at all, as the data is not dynamically changing. You should be able to do this with a set based approach. Below is an example using CTE, with a left join to only insert those that do not exist in the staging table:

;WITH CTE AS
(
SELECT      CAST(ID1 as Varchar(20)) AS ID1, 
            CAST(ID2 as Varchar(20)) AS ID2,  
            'AT' AS [State], 
            CASE When (isAvtiveDate > { fn CURDATE() }) or isAvtiveDate is null Then 1 else 0 end AS isAvtive
FROM        server1.db.dbo.table1 
INNER JOIN  server1.db.dbo.table2 on ID2 = ID1
WHERE       ID3 = 1 AND isActiveDate <= ISNULL(isActiveDate,'2020-01-01')

UNION

SELECT      CAST(ID1 as Varchar(20)) AS ID1, 
            CAST(ID2 as Varchar(20)) AS ID2, 
            'AP' AS [State], 
            CASE WHEN (isActiveDate > { fn CURDATE() }) or isActiveDate is null Then 1 else 0 end AS isActive
FROM        server1.db.dbo.table1 
INNER JOIN  server1.db.dbo.table2 on ID2 = ID1
WHERE       ID3 = 2 AND isActiveDate <= ISNULL(isActiveDate,'2020-01-01')

UNION

SELECT
            Cast(ID1 as Varchar(20)) AS ID1, 
            Cast(ID2 as Varchar(20)) AS ID2,
            'AH' AS [State], 
            CASE When (isActiveDate > { fn CURDATE() }) or isActiveDate is null Then 1 else 0 end AS isActive
FROM        server1.db.dbo.table1 
INNER JOIN  server1.db.dbo.table2   ON ID2 = ID1
INNER JOIN  server1.db.dbo.table13  ON ID2 = ID4
WHERE       ID3 = 5 and toDate is null and fromDate is not null AND isActiveDate <= ISNULL(isActiveDate,'2020-01-01')
)
INSERT INTO StagingTable 
        (
            ID1, 
            ID2, 
            [State], 
            isActive
        ) 
SELECT      DISTINCT 
            CT.ID1, 
            CT.ID2, 
            CT.[State], 
            CT.isActive
FROM        CTE             AS  CT
LEFT JOIN   StagingTable    AS  ST  ON  ST.ID1 = CT.ID1 AND ST.ID2 = CT.ID2
WHERE       ST.ID1 IS NULL 
AND         ST.ID2 IS NULL;

Given the requirement to check each row as the cursor does, I would use the following, using a temp table to check each set of ID1 and ID2 that are identified are unique when inserting into the temp table, then do the insert to the staging table from the temp table:

/*Create temp table*/
IF OBJECT_ID('tempdb..#tmpData') IS NOT NULL DROP TABLE #tmpData
GO
CREATE TABLE #tmpData
        (
            ID1             VARCHAR(20) ,
            ID2             VARCHAR(20) ,
            [State]         VARCHAR(2)  ,
            IsActiveData    BIT
        )

/*Insert into the temp table, with each insert join back to the temp table to ensure ID1 and ID2 are not already inserted*/
INSERT INTO #tmpData
        (
            ID1                     ,
            ID2                     ,
            [State]                 ,
            IsActiveData
        )
SELECT      CAST(ID1 as Varchar(20))    AS ID1, 
            CAST(ID2 as Varchar(20))    AS ID2,  
            'AT'                        AS [State], 
            CASE WHEN (isAvtiveDate > { fn CURDATE() }) or isAvtiveDate is null Then 1 else 0 end AS isAvtive
FROM        server1.db.dbo.table1 
INNER JOIN  server1.db.dbo.table2 on ID2 = ID1
WHERE       ID3 = 1 AND isActiveDate <= ISNULL(isActiveDate,'2020-01-01')

INSERT INTO #tmpData
        (
            ID1                     ,
            ID2                     ,
            [State]                 ,
            IsActiveData
        )
SELECT      CAST(T1.ID1 as VARCHAR(20)) AS ID1, 
            CAST(T2.ID2 as VARCHAR(20)) AS ID2, 
            'AP'                        AS [State], 
            CASE WHEN (isActiveDate > { fn CURDATE() }) or isActiveDate is null Then 1 else 0 end AS isActive
FROM        server1.db.dbo.table1       AS  T1
INNER JOIN  server1.db.dbo.table2       AS  T2  ON T2.ID2 = T1.ID1
LEFT JOIN   #tmpData                    AS  T   ON  T.ID1 = T1.ID1 AND T.ID2 = T2.ID2
WHERE       ID3 = 2 AND isActiveDate <= ISNULL(isActiveDate,'2020-01-01')
AND         T.ID1 IS NULL
AND         T.ID2 IS NULL

INSERT INTO #tmpData
        (
            ID1                     ,
            ID2                     ,
            [State]                 ,
            IsActiveData
        )
SELECT
            Cast(T1.ID1 as Varchar(20)) AS ID1, 
            Cast(T2.ID2 as Varchar(20)) AS ID2,
            'AH' AS [State], 
            CASE When (isActiveDate > { fn CURDATE() }) OR isActiveDate IS NULL Then 1 else 0 end AS isActive
FROM        server1.db.dbo.table1       AS  T1
INNER JOIN  server1.db.dbo.table2       AS  T2  ON  T2.ID2  = T1.ID1
INNER JOIN  server1.db.dbo.table13      AS  T13 ON  T2.ID2  = T13.ID4
LEFT JOIN   #tmpData                    AS  T   ON  T.ID1   = T1.ID1 AND T.ID2 = T2.ID2
WHERE       ID3             =   5 
AND         toDate          IS NULL 
AND         fromDate        IS NOT NULL 
AND         isActiveDate    <= ISNULL(isActiveDate,'2020-01-01')
AND         T.ID1           IS NULL
AND         T.ID2           IS NULL




/*Insert into the staging table from the temp table ensuring only records that are not already in there are inserted.*/
INSERT INTO StagingTable 
        (
            ID1, 
            ID2, 
            [State], 
            isActive
        ) 
SELECT      CT.ID1, 
            CT.ID2, 
            CT.[State], 
            CT.isActive
FROM        #tmpData        AS  CT
LEFT JOIN   StagingTable    AS  ST  ON  ST.ID1 = CT.ID1 AND ST.ID2 = CT.ID2
WHERE       ST.ID1 IS NULL 
AND         ST.ID2 IS NULL;