The 1200mm lens you cite is something of an aberration, since it's built-to-order, not a general-market lens — see Why are some big telephoto lenses so expensive compared to telescopes? and Why are some lenses so expensive?. But the general rule holds true: lenses for DSLRs and most mirrorless cameras are gigantic compared to those in superzoom cameras. There are three general reasons for this:
- The sensor in these superzoom cameras tends to be small — usually 1/2.3" class, which means "thumbnail size". By contrast, the sensors on high-end DSLR are usually the size of traditional 35mm film, and those on mid-range and lower DSLRs and mirrorless cameras still basically in that ballpark, and many times larger than that of the superzoom. This means that the lens has to project a much smaller circle, and can in turn be smaller.
- Not always, but often, those big, heavy lenses have faster maximum apertures. That requires a larger front element (at least!) — which means more glass, more expense, more weight. It would be possible to design a superzoom camera with a faster (wider max aperture) lens, but doing so would make the lens bigger (and probably harder to make with such a gigantic zoom range). That would go against the design goal, and so you don't generally see it. In other words, it's a sort of tautology: superzoom cameras have small lenses with a high zoom range because they do.
- Again not always, but also often: more is expected from SLR lenses, so they are designed to meet higher expectations. All lens design is compromise, in size, weight, cost, or image quality in many different variations — see What image-quality characteristics make a lens good or bad?. Most superzoom designs prioritize that, and either let people live with the results (assuming less picky buyers in that market) or automatically apply extensive software correction.
That amazing zoom range is pretty cool, and the maximum focal length equivalent seems amazing. But, you do pay the price. The smaller sensor inherently gathers less light overall, just because there's less of it. That means more noise, and there's no way to cheat physics on this one. In fact, you could simply crop the center from a DSLR image with one of those big lenses, and probably get a roughly equivalent result, even though the resolution would be nominally lower (unless you have a very high end DSLR). That's because from a practical point of view, zoom is virtually indistinguishable from cropping.
By way of example, here's a crop from an image Kyla Duhamel took from her backyard and posted to Flickr under a CC-BY license, using a consumer-level zoom for a consumer-level DSLR Even at this crop it doesn't fill the frame, but I think the actual detail is roughly comparable to that in the video you linked.
That's with a lens many of us also call a "superzoom" (terminology is awesomely confusing sometimes!), the Canon 18-200mm f/3.5-5.6. This lens weighs about 1.3 pounds and is 4 inches long — Canon's pro-grade 70-200mm f/2.8 (note the reduced zoom range even though both end at 200mm!) weighs more than twice that, at 2.9 pounds, and is almost twice as long, and can probably do somewhat better in detail — but the moon isn't really the primary differentiator between these models. Instead, it's increased sharpness and reduced distortion and other artifacts, faster maximum aperture, more solid build, and so on.
You still do have more size, weight, and cost, though. In exchange, you generally get better image quality outside of that "racked all the way out" situation (and not actually all that much worse in that case.)