Hi FT, 

We would like to request a function where we can avoid HVR doing a truncate before a refresh or deletes before a refresh with a refresh restrict condition. This could be a simple env var to be added in as a action.

The reason for this being, during a conversion of a single partitioned table from Sybase to Sql Server, we wanted to utilize slicing to help parallelize data copies from the table. However we realised that Slicing in the HVR nature is not enabled on Sybase as a source. 

To circumvent this we tried two options, with both options we dropped all indexes on the target tables and declaring each partition on the source as a view. Doing this allows Sybase to use partition elimination avoiding full table scans. 

On top of this we then defined a refresh job with our desired parallelism, put all the views of the 1 table in the refresh, and we defined all the views into one group and applied a table property to say all the views map to the same table name on the target. and let it run, however what we saw initially was that truncates were being ran for each view/slice that completed. 
So we then defined a restrict condition for WHERE 0=1 to avoid the truncates, this in part works however this caused mass blocking as when a slice completes for the next batch to run a superficial delete where 0=1 must run. However it gets blocked by any other Bulk insert still taking place on the table due to them having TABLOCK defined. 

We then also tried Merge, this can work however we want to avoid the join based merges as our datasets are in the terabytes with tables with no indexes this is a pain & causes similar issues with multiple slices & Bulk inserts. 

Ideally we just want to be able to run multiple selects on the ASE source (even if it is a hack) & have a method to just Bulk insert into the target in multiple parts to paralelise our large dataset migrations as possible. Ideally this can be achieved if HVR provides a method to disable truncates or deletes for refresh.