I do not go into the SQL side to ever write things in Epicor. I have plenty of external views that I have written to read, but I use BOs and DMT to do my data injections.
That being said, I have a large amount of data that I need to put into a UD table. There are no customizations for DD or MD through BPMs on this table. Would it still be a no-no to use SQL injection to a UD table? In playing around in a sandbox, it takes about 6 minutes to load 500k+ lines of data. I don’t think DMT will give me that kind of performance.
Disclaimer; this is probably not the official language and is just my experience.
Since the UD table is just that, it shouldn’t be an issue. I have a SQL job that fills out my UD100 table and have been doing this for years and years without issue.
If you’re talking about something like Part_UD, that’s also ok since it’s a UD table joined via a foreign key to the master table, but you’ll only want to alter those UD fields within it vs. messing with sysrowIDs and foreignsysRowIDs, etc.
My opinion is that the general advice of “no touchy SQL” in Epicor is to prevent poor SQL developers screwing up the integrity of the data and thus causing a catastrophe.
What you lose in SQL loading though is you bypass all the BO validations including BPMs, but if that’s appropriate in the use case like mine is, you’ll be fine.
In the Past we had to use SQL for UD because UD would have taken 6 days based on RPMs directly on Server… Where SQL took 30 minutes.
I always say if its _UD and a column you own, both ways you can win, if you know what you are doing. I also never do UD Inserts typically merely updates.
If its an Epicor column, table, I usually stick to BOs and DMT.
I assume this is a one-time deal, now if you are talking about 1000 records, I would just use DMT. But in your case you have this Major Massive Update just like I had.