Im working on a project, where I need to insert various "Products", BUT depending on the type (fixed amount of 3, won't change customer said) the "ID" should start with a certain number (2,3,4)
While my first-thought was to simply use a (in the code removed) Prefix, then the id padded with "0" - this won't work, because an import from an "old" System should be performed, where 3 Tables (rahter than one) have been used.
So, the old system will deliver (from 3 tables) IDs like
200001 | Raw-Material-Table
200002 | Raw-Material-Table
300001 | Composite-Table
300002 | Composite-Table
400001 | Product-Table
400002 | Product-Table
and so on. (it was a "lazy" design, the table where a product was stored was telling apart one of 3 states a product could have - that has been simpliefied to a single column now - all other columns are equal (base-attributes), additional attributes are EAV)
Therefore, the mixed data-set of 3 tables should be inserted into a single table, while maintaining the first "digit" (2,3,4) for future inserts.
So, I would need some sort of "insert" (causing surrogate IDs according to auto-increment), but also create the artificial ID based on the "State".
What options are available for such a scenario? Preferably "less code" and "more query" (if possible at all without code)