I think for APS-C sized sensors, 6-8 MP is ideal, and for full frame 12-16MP. I have no interest in APS-H, frankly. Some wiggle room to crop is nice, but real point of significance here is pixel pitch. Part of the reason the Nikon D3, and D700 have such amazingly low noise at high ISOs is that they have a comparatively low pixel density.
Really, the megapixel race is at this point marketing bullshit. The areas where real improvement is needed is dynamic range and high ISO performance. Nikon has recently edged out Canon in both these areas. The D700 really is the ideal camera for me, except for one tragic flaw (it lacks a Canon lens mount.) I will be watching the reviews of the 5D mk II very closely. If it's dynamic range and high ISO (and auto ISO) are even close to Nikon's, it's clearly my next body. If the D700 bests it, I may seriously consider selling my entire Canon kit and going Nikon. This would be equivalent to Sprtizer going dynamic.
Seriously, there's one thing that will probably keep me in the Canon camp forever: L glass. My 17-40mm F/4L ultrawide is a dream. The 85mm F/1.2L is one of the greatest camera lenses, period. If the brand new 24mm F/1.4L II lives up to its promise, it will be glorious indeed. Nikon is either unwilling or unable to compete with Canon when it comes to fast primes.