A very good piece of software to use is Ulead Video Studio 10. Obviously the software is complicated to use at first, but once you begin to get used to the layout etc. and you learn to use the features well then the results you get will be excellent
.
I tried to use it once, but I don't have the perseverence to learn to use something like that, especially when I wouldn't use it that often anyway.