I use the one that Uncle Bob posted. I've tried others, bigger, more expensive, but keeping going back to the Cajun Injector, as it works the best for me.
I mainly use it to inject turkey breasts that I'm grilling over charcoal on my rotisserie. I use the jarred Cajun Injector marinades (I love the Creole Butter one the best), but it's easy to make your own.
There are pockets of heavier flavor in some meats, with some injections, by some cooks, but they are delicious - kind of like getting a garlic sliver and a rosemary leaf in your slice of roast lamb. It doesn't matter much to the at-home cook.
I've also injected competition pork butts (the way you saw on the Bama Que show - Chris Lilly is an EXPERT injector!), and competition beef briskets (it REALLY makes a huge difference with brisket).
A lot of BBQ competitors inject their meats with mixtures containing a product called FAB. FAB comes in beef, pork and chicken flavors and is a powerful flavor-enhancer. It tastes sort of like powdered bouillion, before you mix it with water or apple juice or whatever your secret liquid is. The do's and don'ts of using FAB are a whole other story.
Here's my brother injecting a brisket at the Massachusetts State BBQ Championship.