Creating Virtual Layer as union of multiple layers with same schema in QGIS

Question

I have a Layer Group surveyor with lots of layers (shapefiles) with seemingly random names, and with identical schemas. I would like to combine them all to one layer; preferably with a Virtual Layer. The following does work.

SELECT * FROM monday
UNION 
SELECT * FROM wed_22
UNION 
SELECT * FROM parking

However, this requires manually typing all the layers. Is there a better solution, where I won't have to manually type the layers?

Answer 1

You can use python to print out the sql needed in the console, then copy paste it to the virtual layer. Add all layers to a group named surveyor and execute:

root = QgsProject.instance().layerTreeRoot()
layerlist = []

#List the layers in surveyor group
for layergroup in root.children():
    if layergroup.name() == 'surveyor':
        for lyr in layergroup.children():
            layerlist.append(lyr.layer().name())

#Print the sql
for n, layer in enumerate(layerlist):
    print("select * from {}".format(layer))
    if n<len(layerlist)-1: #Dont print "union" at the end
        print("union")

Answer 2

Another possible workflow.

Step 1. Apply the "Merge vector layers" tool from the Processing Toolbox (Ctrl+Alt+T)

Step 2. Create a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer... with the following query:

SELECT *
FROM "Merged"