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?

Rizal

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

Counterfoil

These are all great ideas and I think they all work! Thanks! – Rizal

Answer 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

Daigle

Answer 3

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.

Dropkick

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. – Patronage