t-sql 2012 use 1 cte instead of 5 ctes

  • I am currently using 5 separate CTEs and I am wondering if I can use only 1 cte. What I am

    listing below is what 2 ctes looks like. Thus can you tell me if I can use the same cte that

    is listed in the sql below? If so, would you show me how to change the sql listed below to

    use only 1 cte?

    Merge TST.dbo.LockCombination AS LKC1

    USING

    (select LKC.lockID,LKC.seq,A.lockCombo2

    from

    [LockerPopulation] A

    JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type

    JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber =

    LKR.number

    JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID

    JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID

    and LKC.seq = 1

    ) AS LKC2 (lockID,seq,combo)

    ON

    (

    LKC1.lockID = LKC2.lockID

    and LKC1.seq = 1 and LKC2.seq =2

    )

    WHEN NOT MATCHED

    THEN INSERT (lockID,seq,combo) VALUES(LKC2.lockID,2,LKC2.combo)

    WHEN MATCHED

    THEN UPDATE SET LKC1.combo = LKC2.combo;

    ----

    Merge TST.dbo.LockCombination AS LKC1

    USING

    (select LKC.lockID,LKC.seq,A.lockCombo3

    from

    [LockerPopulation] A

    JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type

    JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber =

    LKR.number

    JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID

    JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID

    and LKC.seq = 1

    ) AS LKC2 (lockID,seq,combo)

    ON

    (

    LKC1.lockID = LKC2.lockID

    and LKC1.seq = 1 and LKC2.seq =3

    )

    WHEN NOT MATCHED

    THEN INSERT (lockID,seq,combo) VALUES(LKC2.lockID,3,LKC2.combo)

    WHEN MATCHED

    THEN UPDATE SET LKC1.combo = LKC2.combo;

  • Don't CTEs have to begin with the keyword 'WITH'?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Next, looks like this subquery will always return rows with LKC2.seq == 1, if any.

    (select LKC.lockID,LKC.seq,A.lockCombo2

    from

    [LockerPopulation] A

    JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type

    JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber =

    LKR.number

    JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID

    JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID

    and LKC.seq = 1

    ) AS LKC2 (lockID,seq,combo)

    So MATCHED will never happen in

    MERGE ON .. AND LKC2.seq = 2

    And the query may be refactored to plain INSERT, why you need MERGE at all?

  • I'm seeing two different MERGE commands, no CTEs in sight.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply