Yes you would need a new sim card since the iPhone 4S has a micro sim.. Unless you know how to cut the sim from your 3G to a micro sim then NO. But just go to a ATT store and they will switch it for you!
The 4S will come with a SIM card. You can activate the phone at home. Your 3GS SIM card will automatically deactivate as soon as the 4S is activated. The 3GS may still show bars for a short period, but it will say "No Service" after you reboot.