So is it going to put artists out of work? No, at least not for a while yet. What I can see going away in the very near future is stock photography. Some of the work you can get out of DALL•E in particular can replace a lot of what you see on sites like Shutterstock and Getty. To be honest though, due to saturation of the market, and the lowering payouts of stock photography, this is a dying artform anyway.
Outside of that no, art isn't going anywhere, it's just another tool in the belt like Photoshop. Even concept art which seems to be a really good use case is safe. It may reduce pre-visualisation time and even character creation, but as of right now, there's no way to get a particular character into a scene without using Photoshop.
What I can see coming however is new jobs. Over the next 12 months, it wouldn't surprise me if companies are looking for experts in AI Generated art. Someone who knows how to manipulate the terms used to get exactly what they're after. There are already guides on this, so it's only a matter of time...
So which one is better? DALL•E 2 or Midjourney (or even Stable Diffusion)? To be honest, it really depends on what you're after. If you want realism, photo like pictures, DALL•E 2 seems to be the way to go, however this is also its downfall, sometimes you get outputs that look nothing like you are after.
Midjourney on the other hand seems to work extremely well for digital art, and tends to get a more artistic result (with composition and image) than DALL•E. I have strugged to get anything realistic out of it however, but it gets a lot closer to the asthetic I'm looking for.
One thing I find that both tools seem to struggle with is hands. DALL•E 2 seems to get them right more often than Midjourney, but they both struggle. Some of the other fails end up being quite hilarious at times, or downright creepy.
I'm yet to try Stable Diffusion, I'm signed up to the beta, hopefully I get access soon. Sounds like it could be really good as it runs on your own machine rather than having to pay for access (I think).