question

There are a couple of eproblems with your attempt.

If first CTE has 7 columns, and the second - only 6, then you probably should not use the UNION but opt for UNION ALL instead. This is because you would probably want the seventh column from the first CTE to still appear in the results, opting to use NULL to "fill in the blanks" from the rows of the second CTE which does not have such column. This implies that there is no reason whatever to torture the engine by using the UNION when UNION ALL makes perfect sense.

Also, if the dest column is present in the first CTE and does not exist in the second then I am not sure why you would want to convert it to varchar. Even if you do for some unknown reason, the syntax should be convert(varchar, dest), not convert(dest, varchar). In the list of select columns from the second CTE you should just opt for NULL, and that is all.

Here is the snippet which shows the idea:

;with firstCTE as (
    select 
        Col1, Col2, Col3, Col4, Col5, Col6, dest
        from someTable
),
secondSTE as (
    select
        ColA, ColB, ColC, ColD, ColE, ColF
        from someOtherTable
)
    select
        Col1, Col2, Col3, Col4, Col5, Col6, dest
        from firstCTE
    union all
    select
        ColA, ColB, ColC, ColD, ColE, ColF, null
        from secondCTE;

Something like this will work if the data types for the first 6 columns do match or at least are compatible. The names of the columns in the final select will be driven by the list from the first CTE, so in this example, the column names of the final result will be Col1, Col2, Col3, Col4, Col5, Col6, and dest.

Hope this helps.

Oleg

The remark about the UNION / UNION ALL in the first paragraph of the answer of @Oleg is wrong It has nothing to do with the number of columns in the query. When using UNION ALL it will show all ROWS from the result including any duplicates. When using UNION it remove duplicate rows from the final result, thus only showing unique rows.

When using UNION or UNION ALL (and also the operators like EXCEPT, INTERSECT, etc.) the number of columns of all SELECT statements must match and the datatype of each column must be (implicitly) the same. You can use the technique described by @Oleg and shown in his code snippet to add additional "dummy" columns to the second (and optional third, fourth, etc.) SELECT statement to match the number of the first SELECT.