Add (only) geometries to table without geometries from other layer based on common field

Question

I'm looking for a processing tool or virtual layer to do this:
I have a table without geometries with lots of fields (mssql), including a cadastral reference. There is a database (with geometries) from another provider that includes the cadastral reference and more fields, but I only need the geometry from this source.

I would like to get a new layer with all the attributes from the first table plus the geometry from the second database. I know how to do it with joins, but I need something more authomated because the second layer is updated several times a year. I've tried to get the geometry by expression with no success.

Answer 1

A virtual layer seem the best if there are frequent update. (note that virtual layer may be slow, so if the update are not too frequent it could be wise to export the virtual layer to work with till the next update)

The SQL query would be in the form below (adapt it to your exact need)

SELECT
    t.field_1
  , …
  , t.field_n
  , g.geometry
FROM
    table_without_geometry AS t
    JOIN
        database_with_geometry AS g
        ON
            t.cadastral_reference=g.cadastral_reference

If you want to directly get a new layer you may use a similar SQL query with the Execute SQL tool

Runs a simple or complex query with SQL syntax. Input data sources are identified with input1, input2, ..., inputN and a simple query will look like: SELECT * FROM input1 The result of the query will be added as a new layer.