What is the difference between automatic transmission oil and oil used in a manual tranmission?

It depends on the transmission. Automatic transmissions are fluid-drive (hydraulic) units that require oil that is highly resistant to heat and that lubricates valves, etc at high pressures and temperatures. For this reason the oil is fairly thin and watery. Manual transmissions are not hydraulically operated and thus need an oil that actually lubricates bearings, gears, syncronizers, shift rails, etc. There are actually some manual transmissions that are engineered to use ATF (automatic transmission fluid).