How would I go about getting the following select into a Declared @TEMP TABLE?
Notice that the ;with cte also is joined with another select in a union all statement
;WITH CTE (
[Client ID] ,
[Contact ID] ,
ContactType ,
fullName ,
[Last Name] ,
Email ,
Phone ,
[Phone ext] ,
Role ,
Title ,
ur ,
Submitted ,
Locked ,
ConPCT ,
Assignment ,
Working ,
Pending ,
AddRemove ,
ContactVisitSort ,
id )
AS
(
SELECT vw.[Client ID],
vw.[Contact ID],
vw.ContactType,
vw.fullName,
vw.[Last Name],
vw.Email,
vw.Phone,
vw.[Phone ext],
vw.Role,
vw.Title,
vw.ur,
vw.Submitted,
vw.Locked,
vw.ConPCT,
vw.Assignment,
vw.Working,
vw.Pending,
(
CASE WHEN ISNULL(ContactV.id, 0)= 0 AND ISNULL(vw.[Contact ID],0) > 0 THEN 'Add'
WHEN ISNULL(ContactV.id, 0)> 0 AND ISNULL(vw.[Contact ID],0) > 0
AND NOT Exists (Select 1 from dbo.ClientVisitTrackingContacts WHERE vw.[Contact ID] = ContactV.[Contact ID] AND visitID = A.id ) THEN 'Add'
WHEN ISNULL(vw.[Contact ID],0) = 0 THEN ''
ELSE 'Remove' END
) AS AddRemove,
(
CASE WHEN ISNULL(ContactV.id, 0) = 0
THEN 0
ELSE 1 END
) AS ContactVisitSort,
A.id
FROM dbo.vw_MarketingVisitationContacts AS vw
LEFT OUTER JOIN
dbo.ClientVisitTrackingContacts AS ContactV ON vw.[Client ID] = ContactV.[Client ID] AND vw.[Contact ID] = ContactV.[Contact ID]
OUTER APPLY
(
SELECT id FROM ClientVisitTracking CVT
WHERE CVT.[Client ID] = vw.[Client ID]
) A
WHERE (LEN(vw.fullName) > 0)
)
SELECT * FROM cte WHERE id = @VisitID
UNION all
SELECT [Client ID] ,
[Contact ID] ,
'Contact' ContactType,
ISNULL([First Name],'') + ' ' + [Last Name] FullName,
[Last Name],
Email,
Phone,
[Phone ext],
Role ,
Title ,
2 ur,
0 Submitted,
0 Locked,
0 ConPCT,
0 Assignment,
0 Working,
0 Pending,
'Add' AddRemove,
0 ContactVisitSort,
@VisitID id
FROM dbo.[Client Contacts] C
WHERE NOT EXISTS (
SELECT 1
FROM CTE C1
WHERE C.[Client ID] = C1.[Client ID]
AND C.[Contact ID] = C1.[Contact ID]
)
AND C.[Client ID] = @ClientID
ORDER BY ur,AddRemove DESC, Assignment DESC,[Last Name]
Select all Open in new window