How can I import data from SQL using an import scenario with an Order By clause? Or what work arounds have you come up with?
Most situations I’ve just made sure the data is in SQL in the order I want it, but sometimes that’s a challenge with complex views.
The issue is it creates conflicts where there are many detailed rows but the data is not in order. Setting Save to Each row or Batch size to 1 doesn’t help either.
The help documentation specifically says Order By cannot be used in the command fiels, so I’m guessing this is a common issue.
Answer
Order By with a SQL Data Provider
+6Best answer by bryanb39
One of the source tables was missing a PK so it wasn’t indexed. Once I added that it started cooperating. I’ll leave this open for a bit in case someone has found another way to make this work. The ability to use query would be very helpful.
One option might be to layer an additional view on top of the one that you’re trying to import and it’s only purpose is to call the view doing the hard work and ordering the output. Then adjust your import scenario to call the simplified view.
+6I tried that and it’s still out of order, I even tried building a function and then a view using the function.
+6One of the source tables was missing a PK so it wasn’t indexed. Once I added that it started cooperating. I’ll leave this open for a bit in case someone has found another way to make this work. The ability to use query would be very helpful.
https://www.fiverr.com/acudatica
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.