CODEWITHSUNDEEP
t-sqlsql-server-2008except
salvationishere
salvationishere

Reputation: 3511

How to insert into a static table using EXCEPT?

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?

Upvotes: 8

Views: 18631

Answers (3)

Ricardo
Ricardo

Reputation: 1

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.

Upvotes: 0

podiluska
podiluska

Reputation: 51504

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

Upvotes: 2

Rodney Adams
Rodney Adams

Reputation: 291

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

Upvotes: 16

Related Questions