Many people ask this question since we all have bad memories of getting painful injections as children. But the answer to this question is different for everyone because everyone’s threshold and perception of pain is different. One person might perceive a given level of stimulation to “hurt” while another might not really feel much at all.
Unlike our farming ancestors, most of us in this day and age are not used to feeling any sort of physical sensory stimulation. Most of us work in an office type environment, where a paper cut is typically the worst sensation one might encounter. Similarly, domestic life has been domesticated to the point where a slight kitchen or yard-work accident provides the most sensation one might have to endure. From the traditional point of view, to be effective acupuncture should produce a level of stimulation that feels like a dull, achy pressure or tingling sensation. Again, to some people this sensation is interpreted as “hurt” while others are just aware of it and find it curious.
Although sharp pain is not necessary (or desirable) for good results, modern research is bearing out the traditional viewpoint that a moderate level of sensation is more effective than sense-less acupuncture. Here is an example of a currently study that supports this conclusion: http://www.sciencedaily.com/releases/2013/02/130225122037.htm.