How to insert into a static table using EXCEPT?

tsql sql-server-2008 except
salvationishere picture salvationishere · Aug 13, 2012 · Viewed 13.1k times · Source

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

Rodney Adams picture Rodney Adams · Aug 13, 2012

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

Related questions

Why does EXCEPT not work? Incorrect Syntax near the Word Except

SELECT SKU FROM PartProductSupplemental EXCEPT SELECT SKU FROM Inventory Why do I get this error: Incorrect Syntax near the Word Except I check on line and syntax is syntactically correct: SELECT ProductID FROM Production.WorkOrder EXCEPT SELECT ProductID FROM Production.…

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More