user584018 user584018 - 3 months ago 15
SQL Question

looking for cursor replacement

I have 2 tables

#TblFinal
and
#TblData
.. I need to compare #
TblData
data with"#
TblFinal
based on unique key
F_U_KEY vs D_U_KEY.



  1. if #
    TblData
    data for a unique key is not exist in #
    TblFinal
    , then #
    TblData
    data record will insert into #
    TblFinal
    table.
    F_COUNTER
    will be 1 (default) and
    F_IS_CLEAR
    is 0 default

  2. if #
    TblData
    data for a unique key is exist in #
    TblFinal
    , then first we get key
    F_ID
    from #
    TblFinal



case A: if D_SEV_ID of table "#TblData" is not equal to -11

then need to update "F_COUNTER" for table "#TblFinal" for "F_ID"

case B: if D_SEV_ID of table "#TblData" is equal to -11

then new entry for D_SEV_ID = -11 and F_IS_CLEAR =1 for "F_ID"

Here is the cursor I wrote, need some optimized solution as my actual #
TblFinal
having huge data and data compare table "#TblData" always have 100 records to compare.

CREATE TABLE #TblFinal
(F_ID INT IDENTITY(1,1), F_VAL NVARCHAR(20), F_SEV_ID INT, F_U_KEY NVARCHAR(200), F_COUNTER INT DEFAULT(1), F_IS_CLEAR BIT DEFAULT(0))


CREATE TABLE #TblData
(D_ID INT, D_VAL NVARCHAR(20), D_SEV_ID INT, D_U_KEY NVARCHAR(200))

INSERT INTO #TblData VALUES(1, 'test 1', 2, '1:100002135::::15124:9334'), (2, 'test 1', 2, '1:100002135::::15124:9334'),
(3, 'test', -11, '1:100002135::::15124:9334'), (4, 'test 1', 2, '1:100002135::::15124:9334'), (5, 'test 1', 2, '1:1024:9334')


DECLARE @D_ID INT
DECLARE @D_SEV_ID INT
DECLARE @D_U_KEY NVARCHAR(200)

DECLARE A_CUR CURSOR FOR
SELECT D_ID, D_SEV_ID, D_U_KEY FROM #TblData ORDER BY D_ID ASC
OPEN A_CUR
FETCH NEXT FROM A_CUR INTO @D_ID, @D_SEV_ID, @D_U_KEY

WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS(SELECT 1 FROM #TblFinal(NOLOCK) WHERE F_U_KEY = @D_U_KEY AND F_IS_CLEAR = 0 AND F_SEV_ID <> -11)
BEGIN
DECLARE @FId INT
SELECT @FId = F_ID FROM #TblFinal(NOLOCK) WHERE F_U_KEY = @D_U_KEY AND F_IS_CLEAR = 0 AND F_SEV_ID <> -11
--IF @D_SEV_ID != -11
IF (@D_SEV_ID <> -11)
BEGIN
UPDATE #TblFinal
SET F_COUNTER = F_COUNTER + 1 WHERE F_ID = @FId
END
--IF @D_SEV_ID = -11
ELSE IF(@D_SEV_ID = -11)
BEGIN
INSERT INTO #TblFinal (F_VAL, F_SEV_ID, F_U_KEY)
SELECT D_VAL, D_SEV_ID, D_U_KEY FROM #TblData(NOLOCK) WHERE D_ID = @D_ID

UPDATE #TblFinal
SET F_IS_CLEAR = 1 WHERE F_ID = @FId
END
ELSE
BEGIN
PRINT 'DO NOTHING'
END
END
ELSE
BEGIN
INSERT INTO #TblFinal (F_VAL, F_SEV_ID, F_U_KEY)
SELECT D_VAL, D_SEV_ID, D_U_KEY FROM #TblData(NOLOCK) WHERE D_ID = @D_ID
END

FETCH NEXT FROM A_CUR INTO @D_ID, @D_SEV_ID, @D_U_KEY
END

CLOSE A_CUR
DEALLOCATE A_CUR


--SELECT * FROM #TblData
SELECT * FROM #TblFinal
DROP TABLE #TblData
DROP TABLE #TblFinal

Answer

You should be able to do this set based. Here is one way to tackle this problem. The only part I don't really understand is F_IS_CLEAR. The business rule for that is a bit fuzzy. If you need help with part I need to understand what the rationale is for that.

CREATE TABLE #Output
(F_ID INT IDENTITY(1,1), F_VAL NVARCHAR(20), F_SEV_ID INT, F_U_KEY NVARCHAR(200), F_COUNTER INT DEFAULT(1), F_IS_CLEAR BIT DEFAULT(0))

insert #Output
    select x.D_VAL
    , x.D_SEV_ID
    , x.D_U_KEY
    , x.F_COUNTER
    , x.F_IS_CLEAR
from 
(
    select D_VAL = MIN(D_VAL)
        , D_SEV_ID = MIN(D_SEV_ID)
        , D_U_KEY
        , F_COUNTER = COUNT(*)
        , F_IS_CLEAR = 1
        , SortOrder = 1
    from #TblData d
    where D_ID < (select d2.D_ID from #TblData d2 where d2.D_U_KEY = d.D_U_KEY and d2.D_SEV_ID = -11)
    group by d.D_U_KEY

    UNION ALL

    select D_VAL
        , D_SEV_ID
        , D_U_KEY
        , F_COUNTER = 1
        , F_IS_CLEAR = 0
        , SortOrder = 2
    from #TblData d
    where d.D_SEV_ID = -11

    UNION ALL

    select D_VAL = MIN(D_VAL)
        , D_SEV_ID = MIN(D_SEV_ID)
        , D_U_KEY
        , F_COUNTER = COUNT(*)
        , F_IS_CLEAR = 0
        , SortOrder = 3
    from #TblData d
    where D_ID > (select d2.D_ID from #TblData d2 where d2.D_U_KEY = d.D_U_KEY and d2.D_SEV_ID = -11)
    group by d.D_U_KEY

    UNION ALL

    select D_VAL = MIN(D_VAL)
        , D_SEV_ID = MIN(D_SEV_ID)
        , D_U_KEY
        , F_COUNTER = COUNT(*)
        , F_IS_CLEAR = 0
        , SortOrder = 4
    from #TblData d
    where NOT EXISTS (select d2.D_ID from #TblData d2 where d2.D_U_KEY = d.D_U_KEY and d2.D_SEV_ID = -11)
    group by d.D_U_KEY
) x

order by D_U_KEY, SortOrder

select * from #Output
Comments