If the invoice in your example is created when order is confirmed,
then POST to /order/123/confirmation could point to the /invoices/123
resource. The only step I would add one GET in the above sequence of
request to discover the confirmation resource.

Please do excuse me if I have mistaken in understanding any point of yours :).

Now coming to your original post - INSERT INTO TABLE SELECT * FROM....
I could map it to the above example as INSERT INTO invoice SELECT FROM
order where order id = 123
In HATEOAS, AFAIK, it would not be a violation if server parses any
URI. I would map it as