How To Use Delete With Except Clause?

I'm trying to do some ETL from some single tenant DBs (per client) to a multi-tenant DB (all clients, or 'global'). In the global DB, I'm trying to maintain a lookup table that id

Solution 1:

The reason it isn't working is that you're actually running two statements sequentially. Think of your code more like:

DECLARE@ClientIDvarchar='ClientA'DELETEFROM Global.dto.ClientUsers;

SELECT ClientID, UserID FROM Global.dto.ClientUsers WHERE ClientID=@ClientIDEXCEPTSELECT ClientID=@ClientID, UserID FROM ClientA_DB.dbo.Users;

If you want to modify the Delete statement, you need to follow it with a Where, Join, etc.

For some alternative methods to get the result you want, see the excellent answers at: Using T-SQL EXCEPT with DELETE / Optimizing a query