You need to use another SQL statement (DML, so insert, update, insert or merge) that actually uses the CTE.
WITH CTE_Test (ID)
AS
(
SELECT CAST(id AS VARCHAR(60)) FROM cat_transaction
EXCEPT
SELECT ext_reference FROM Patricia.dbo.BUDGET_LINE
)
SELECT * FROM CTE_Test;
Also, the semicolon for the WITH is only necessary if there are other statements preceding the CTE. And you should terminate them with a semicolon, not start the CTE with one.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP