I know there are a lot of common misconceptions about megapixels and their actual utility, but does more megapixels mean I can zoom into a macro shot of say an insect and see more details compared to a lower megapixel shot?
Yes - if you took the same shot using the same lens on two cameras, one with 6 megapixels and one with 12, you would be able to crop the larger image, effectively zooming into the image.
There are a few things to bear in mind:
12 megapixels is not twice the size of 6 megapixels - it's only 41% bigger along each side.
The image quality at the pixel level is not guaranteed to be the same. Essentially more megapixels means more "photosites" in the same physical space, which mean each photosite is not getting as much light and has to be more sensitive. There are also problems with diffraction and chromatic aberration. This might result in lower quality.
As general a rule as there can be, more megapixels is good as long as you're not light limited. Smaller pixels are noisier (by virtue of gathering less light each), but if you you have plenty of light this may be neglected.
Now some people are going to claim that increasing megapixel counts is only worth it when using the best lenses but theoretically this is not the case. The resolving power of a system (i.e. sensor plus lens) is the product of the resolving power of the lens and sensor, therefore by keeping the same lens you can make gains by increasing the resolving power of the lens. You will get into diminishing returns however as you increase the resolving power of the sensor for a fixed lens.
There are also arguments that a larger number of noisier pixels no worse for noise when you normalize for the total pixel count (when you average pixels noise gets averaged out) i.e. the only thing that matters is total light gathering area. This agrees with the theory but I'm yet to see any compelling evidence.
It's not any more better than with any other lens type.
Since more pixels on an equally sized sensor just means you get a higher resolution, you can expect exactly the same as with any other lens: You will be able to "digitally zoom in" in post-production, but beyond that, the point is moot.