How to insert into a static table using EXCEPT?
14,470
Solution 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
Solution 2
Try this instead
insert rd_scs_only_clients (yourclientfieldname)
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')
)v
If that's not doesn't work, there may be a problem elsewhere. Try the SQL without the insert section
Author by
salvationishere
Updated on June 11, 2022Comments
-
salvationishere almost 2 years
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?