with cte union all select

Question

I have two tables Arrears and Invoices . I am trying to do the following:

;with Acc.. as ( select ....from ....)

select ..... from  Arrears 

UNION ALL

select ... from Invoices 

the problem is that results are like :

A header	B header
row	row
row	row

the output should be like this:

Table	B header	c header
Arrays	row	row
Invoices	row	row

Answer 1

You could add a string literal to each query to indicate what table it came from:

SELECT 'Arrays' AS table_name, *
FROM   arrays
UNION ALL
SELECT 'Invoices' AS table_name, *
FROM   invoices