Hi Team,
We want to migrate the database from Production environment to UAT environment for one OutSystems application.
We are think of below solution-
Export the application specific table data from PROD and Import the data in UAT environment.
We have relational database tables with referential integrity. If we do direct database import in UAT then we will get integrity errors for foreign keys, as our all ID (Primary Key) columns are set to Is AutoNumber = Yes.
Because all the parent table ID values will be changed after data import and we need to adjust the reference (foreign key) column values as per new value.
We are thinking to enable the IDENTITY INSERT ON in the UAT environment but it seems we will not have access to execute the command: SET IDENTITY_INSERT ON.
We have received below response from OutSystems-
We have tried to change Is Auto Number = No for temporary, but we got below error while publishing the app. (Database Autonumber columns can not be changed)
Could you please guide us with the possible solutions?
Best Regards,
Nitin
Hi Nitin,
Add a Source Id field to the parent table. You will need to import data Sequentially first import all the parent data and store the production id in the Source Id filed. Next import the Child data and map the new foreign key by searching with the Production FK= Parent source Key.
you may consider exposing an API for Exporting the Data and consuming the API to create the Data with the logic mentioned above also if this is a frequent activity.
Let me know if you need further assistance.
Regards
Subu