There is option to change the datasource connection of existing metadata.
- You can edit any already created metadata
- Change the datasource connection of that metadata. I have attached the screenshot which shows the place from where you can change the datasource connection.
- “Save As” the metadata with some other name at any location.
- Refer to the following blog for more details https://www.helicalinsight.com/how-to-edit-metadata/
This new metadata which you had SAVE AS is copy of all joins, tables, views etc with just different database connection.
This above method is from front end UI.
You can have same method from back end as well.
- Open .metadata (physical file) from backend in any text editor.
- Make copy of this file i.e. Save As it with some other name with extension .metadata at same location.
- In this .metadata which is copy, change the connection of database and save the file.
If you switch the datasource from 1 to datasource 2 and the second datasource is also having simiar tables in it then it will work without any problem.
Eg. If tables are like databaseA.table1 and databaseB.table1, then simply switching the database connection and saving the metadata will work without any issues.
If you have written certain custom views then the tables and column names which you have called in First Connection if they are present in second connection then it will work fine.
There is also an option of changing the schema and catalog names also when switching from one connection to another.