What's the best and optimized way to copy the whole content of View_Legacy F

@patrick Hi, a scheduled process that makes use of a Query DB node with a query which inserts the unique records from View_Legacy (but aren't present in Table_Appian) into Table_Appian, should do the work and infact this works faster. The frequency of the scheduled process should be set considering the updates from external system. It can be run frequently (for example two hours a day) provided if you are not expecting huge volumes and a straight forward insertion of unique records. Also bear in mind that more frequency and huge volume of records can together consume a significant system of resources, thereby affecting the performance.

Also a wrapper process model can be built around this scheduled process to trigger the updates on need basis and expose the same as an 'Action' to the Administrators.

The only tradeoff of this approach is that, you need to write a query which is platform dependent. If you prefer to stay away from writing a sql query, probably you can think of an alternative like this - Build a new view that contains records from View_Legacy but are not in Table_Appian. In the scheduled process, retrieve the records from new view and run a 'Write to data store entity' smart service to insert the results into Table_Appian. Again your query rules should handle worst case scenarios probably generated because of huge volume of records as a result of updates.

Added to the way, I don't think there is a OOTB or shared component already available, but it depends on the way how best we can make use of features in Appian.

Let's see if other Appian practitioners could come up with much better solution(s).

*Added to the above - Sorry for the typo.

First of all thanks as usual Sikhi! I totally agree and this is equal to my thoughts.
In addition my idea is using Appian as Orchestrator and getting the database to do the work as I don't want to get Appian to transfer large amounts of data could because of potential memory/stability issues.
The challenge here is that we have to transfer and compare large amounts of data (more than 1000 rows) at 30 mins freq and, a part from the readOnly view, we can't have the control of the legacy Database (because of permissions, rights, approvals ecc.).
In order to keep the view and table in sync this procedure should be completed in one shot.

The best case would consist in having a shared component which, according to two tables, could produce in output only the different rows. In this way, I would be able to get at least a little subset of data for elaboration/update.

@patrick No problem :-). Understood, still you can give a thought over 'Query DB' node with having a native SQL query in it. Even we do have this 'comparison and insertion' kind of implementation over hundreds of thousands of records and we can see it being done in few minutes. So, in your case, I don't think it should be an issue, as it's just thousands of records.

Out of interest, may I know how many columns should be actually updated? And how many tables should be updated?

Sure! In our case we have two tables and the bigger one has 50 columns. At least 30 columns could be updated

Hmm, If you want to do this in Appian,an efficient SQL query in combination with testing the functionality with the maximum expected frequency of records can give you assurance that this can be brought into Appian. As it's just one single SQL statement, it shouldn't be a problem and AFAIK it is doable.