How to include layer names when union'ing in virtual layers?

Question

I am creating a virtual layer in QGIS using the following statement:

SELECT * FROM (
SELECT * FROM a UNION ALL
SELECT * FROM b UNION ALL
SELECT * FROM c
) AS merged_tables

What is the best way to add a column to merged_tables containing the layer name of the source tables (in this case: a, b, c)? It would also be nice to be able to include the path to the source for a, b and c.

Answer 1

The following allows you to add an additional column with a string of your choice:

SELECT 'My String' AS Column_Name FROM Table_Name

So the code in the question becomes:

SELECT * FROM (
SELECT * , 'a' as Source_Table FROM a UNION ALL
SELECT * , 'b' as Source_Table FROM b UNION ALL
SELECT * , 'c' as Source_Table FROM c
) AS merged_tables

Unfortunately, I have not found a way entering the table name or path other than by manual input.