How can I add an identifier 'flag', in a new column on a temporary table, as part of a CTE expression in SQL Server 2017?

Question

I am a SQL Server beginner and have constructed a WITH statement to perform a dataset comparison exercise.

As I am performing a series of manipulations, firstly consolidating my main dataset A from a number of secondary tables, and then reconciliation, and then filters to check for specific conditions, I thought it would be best to use a CTE for this purpose.

Now, I am at a stage where I've identified a few categories of exceptions, that I'd like to throw into a final table at the end of the statement, and would like to label them in a fresh column as I go along. See example below:

Exception_Table_1
(col1, col2, col3, col4, col5)
AS
(
SELECT * FROM Source_Table WHERE <filter here>
)
;

Exception_Table_2
(col1, col2, col3, col4, col5)
AS
(
SELECT * FROM Source_Table WHERE <filter here>
)

INSERT INTO
destination_table
SELECT * FROM Exception_Table_1
UNION ALL
SELECT * FROM Exception_Table_2

The table structure, data types are exactly the same across the source table and the resulting exception tables are infact filtered views of that source table. As an additional complication, the exception tables are being further segmented but I'll leave that for now.

My question is - can I add a new column in both Exception Tables 1 and 2, that has a default value of '1' and '2', indicating the nature of the exception, which can also be copied into the destination_table?

My code works as shown above - but as there is no identifier, I've also had to create 'real' tables for exception tables 1 and 2, just to count the numbers in each category - which kind of defeats the point of using a CTE in the first place.

I've looked elsewhere in the forum, and no, the use of ALTER / UPDATE doesn't seem to help.

SQL Server 2017 btw.

I look forward to your responses and thank you so much for taking the time to help; much appreciated!

Answer 1

UPDATE: Problem Solved :) Thanks a lot Jonathan - however, that syntax didn't work am afraid. I ended up using the CAST function, as follows: SELECT *, CAST('default_value' AS varchar(8)) AS col6 FROM.... etc. in every exception table CTE. The result was a neatly labelled set of exceptions to analyse, and it also means I have all the data I want in 1 table instead of 5 :)