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?

score 15 · Accepted Answer · answered Aug 13 '12 at 20:01

15

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

answered Aug 13 '12 at 20:01

Rodney Adams

281
3
6

These are all great ideas and I think they all work! Thanks! – salvationishere Aug 13 '12 at 20:34

score 2 · Answer 2 · answered Aug 13 '12 at 20:01

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

Ricardo · Answer 3 · 2023-06-26T14:51:28.327

0

My favourite way of doing this sort of thing is to select into a recovery table from the source table except the target table.

Although there is then the second-step of inserting into the target table everything from the recovery table.

That way you can back out (of either step) with confidence if needed.

edited Jun 26 '23 at 14:51

answered Jun 26 '23 at 14:41

Ricardo

1
1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jun 28 '23 at 19:19

How to insert into a static table using EXCEPT?

3 Answers3