by David Crowther
Question:
How can you transfer the Layer Styles table from one Database to another Database?
Answer:
The simplest way to copy the Layer Styles table from one database to another is to use QGIS. Follow the steps below and you can transfer your table too!
In the example below we will transfer an existing Layer Styles table from a Postgres database to a new SQL database.
1 – In QGIS connect to the current database that contains the current Layer Styles table.
Use QGIS and connect to your current database but ensure that in the connection settings you tick the option for Also list tables with no geometry.
When you connect via the DB Manager tool, you can now see the Layer Styles table in the list.
Note – if you don’t see the Layer Styles table ensure that you have saved at least one style into your current database, as this will then auto create the Layer Styles table.
Once you have dragged it into QGIS and the table is open it will be listed in your Layer Panel.
2– Drag and drop the Layer Styles table into your new Database.
In QGIS select the Layer Styles table and drag it into your new Database. In this example we are going to import it into a SQL Database.
When tyring to import the Layer Styles table into a SQL database, you may get an error message saying the useAsdefault field cannot be imported.
So, by saving the Postgres Table as a temporary GIS file e.g. a GeoPackage file, you can drop the useAsdefault column.
This time if you drag and drop the GIS file version of the table, it will successfully be imported into your SQL database.
Once it has been uploaded into the database, we can select the table to view the records.
3– Update the Layer Styles table.
Now that the Layer Styles table has been imported into your new database, you will need to edit some of the values to reference the new database. For example:
- f_table_catalog – change to the new database name
- f_table_schema – change to dbo for a SQL database
Finally, we will need to update the Design of the table now that it is in the new SQL database:
- to add back in the UseAsdefault field
- and also remove the Type field as SQL does not need this
4 – Open a Table from the new Database and use the Saved Style.
Now if you open a table from the new (SQL) database, and it has a valid reference in the Layer Styles table, when it opens it will be auto styled!
By transferring the Layer Styles table to your new Database, this means that you won’t need to recreate and save the individual Styles for each of your tables…. Hopefully saving you lots of time and effort!