Which new insight did $E=mc^2$ give us? I had a special relativity course at university. Now I'm trying to extract what new insight $E=mc^2$ did give us. I mean that moving mass has/is energy (kinetic) not new. The energy merely changed from classical kinetic energy to some relativistic form. And the offset with rest mass doesn't matter for energy.
I'm hoping for some in-depth answer.
Given a few hours I could probably recall physics myself, but I suppose someone will have a nicer, more fundamental answer :)
 A: 
And the offset with rest mass doesn't matter for energy.

Tell it to the relatives of those who were evaporated in Hiroshima and Nagasaki. 
The contribution to the energy from the rest mass is the very point of $E=mc^2$. And it matters because the rest mass of objects that exist may be changed, too. In fact, only one conservation law – the total energy i.e. the total mass conservation law – holds. All other processes that are compatible with this single law (plus other laws such as the conservation of momentum, angular momentum, and electric charge) are allowed. This allows the transformation of what we used to call "energy" to things that we used to call "mass" or "matter" and vice versa.
When we use nuclear energy, whether peacefully or less peacefully, we are changing the uranium to some other elements whose rest mass is about 0.1 percent smaller. So we may extract $0.001 mc^2$ of the energy. That makes it enough for a few pounds of a material to evaporate a city or for a few tons of a material to power a nation for quite a long time. Thermonuclear fusion increases this efficiency to 1% or so, a fact that gives us all the useful energy (from the Sun) and that may be reproduced in future thermonuclear fusion plants, too. If we annihilate particles with their antiparticles, we may convert the full $E=mc^2$ where $m$ is the total rest mass into energy (e.g. the kinetic or potential energy of a rocket) and it's a lot of energy.
And on the contrary, the LHC accelerates beams of protons. The kinetic energy of the protons, 4 TeV per proton and 8 TeV for a pair, may be converted to any other form of energy, including the energy stored in the rest mass of new particles. So by the collision of these two protons, or any other particles, we may create 10 new quarks and a Higgs boson or anything else that is allowed by the total energy conservation law.
What we used to call "energy" and what we used to call "mass" is the same thing and the different forms may be transformed to each other much like mechanical work may be transformed to heat and vice versa. It's clearly one of the deepest insights of the 20th century science.
A: I'm not sure what you meant by 'in-depth/fundamental' that Lubos' answer didn't cover but let me offer an alternative. The equation shown us that energy and mass are two forms of the same thing. Mass can be converted into energy and the exchange rate for this conversion, is c squared. If by fundamental you are looking for a derivation, Einstein 'stumbled upon' this expression while deriving an invariant formula for momentum in spacetime. When you examine the time component of the energy momentum four vector, E=mc^2+1/2mv^2 pops out
