SQLTeam.com | Weblogs | Forums

T-sql 2012 use 1 cte instead of 5 ctes


#1

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;