It really depends on why exactly they think SA is required.

One option if they absolutely have to have SA is to just give them their own instance and...

I don't think the two can be separated at least not right now.  Very very few companies and that number decreases substantially as company size increases are using out of...

Well are you getting just the concatenated data or do you have access to the base fields, if so just sort on those in the desired order.

One option is to import the data into a staging table without whatever constraints are causing you issues, then from there load the valid data into your final table and...

What about this?

WITH TEMP_CTE AS(
SELECT *, LEAD(VisitDate, 1, '29990101') OVER(PARTITION BY PID ORDER BY VisitDate ASC) AS NextVisit FROM #Person
)
SELECT TEMP_CTE.PID, TEMP_CTE.VisitDate,...