Ok - it works like this. The first thing the dentist will do is put a bit of topical numbing gel on your gums where he/she is going to inject the primary anesthetic. After that has had a minute or two to work, he'll inject your gums. The location depends on which tooth he's going to fill. It does hurt for a second, but as soon as the anesthesia hits the nerves, they're OUT. The topical agent and the injected anesthesia taste pretty lousy, but I'm sure you aren't worried so much about that, as it's minor. The full effect will take a few minutes to work, so you'll probably just sit there while that happens; your lip or cheek may feel cold when it's really numb, and it may feel really big, even though it's not :-)
After the anesthesia wears off (which takes a few hours), the injection site may be a little sore or stiff (especially if it had to go into the jaw muscle for a back tooth), but that will be gone in a day or two. The tooth itself won't hurt at all. You can take a dose of aspirin or Tylenol if it bothers you; rinsing with warm salt water helps the site heal, too.
The needle WILL look big compared to the kind that they'd use for a vaccine; that's because they generally use a different type of syringe that can be autoclaved so that it's sterile. If you don't want to look, then close your eyes.
If ANYTHING hurts while the dentist is working, tell him. He is interested in your comfort, because the more comfortable you are, the simpler it is for him to do his work. If he offers nitrous oxide (a gaseous anti-anxiety medication), and I'd definitely accept it; it works very well for most people.