How to insert into a static table using EXCEPT?

Question

I am developing an SSMS 2008 R2 T-sql query. Currently my query looks like:

INSERT rd_scs_only_clients
SELECT DISTINCT en.clientid_c
FROM cd.enrollments en 
WHERE en.agency_c in ('SCCR','SCRP') AND DATEDIFF(DAY, GETDATE(), startdate_d) > 90
EXCEPT
SELECT DISTINCT en.clientid_c
FROM cd.enrollments en 
WHERE en.agency_c not in ('SCCR','SCRP')

but this results in 0 records because it doesn't seem to be recognizing all of the code below the INSERT statement as belonging to the same query. How can I rewrite this?

Answer 1

Wrap your statement in select * from and it should work.

INSERT rd_scs_only_clients

select * from (
SELECT DISTINCT en.clientid_c
FROM cd.enrollments en 
WHERE en.agency_c in ('SCCR','SCRP') AND DATEDIFF(DAY, GETDATE(), startdate_d) > 90
EXCEPT
SELECT DISTINCT en.clientid_c
FROM cd.enrollments en 
WHERE en.agency_c not in ('SCCR','SCRP')
)DATA