How to move tables from one sql server database to another?

To do this really easily with SQL Server 2008 Management Studio:

1.) Right click on the database (not the table) and select Tasks -> Generate Scripts

2.) Click Next on the first page

3.) If you want to copy the whole database, just click next. If you want to copy specific tables, click on "Select Specific Database Objects", select the tables you want, and then click next.

4.) Select "Save to Clipboard" or "Save to File". IMPORTANT: Click the Advanced button next to "Save to File", find "Types of data to script", and change "Schema only" to "Schema and data" (if you want to create the table) or "Data only" (if you're copying data to an existing table). This is also where you'd set other options such as exactly what keys to copy, etc.

5.) Click through the rest and you're done!

If you're moving the tables to a whole new database just because of growth, you might be better off considering using filegroups in your existing database instead. There will be a lot fewer headaches going forward than trying to deal with two separate databases.

EDIT

As I mentioned in my comments below, if you truly need a new database, depending on the total number of tables involved, it might be easier to restore a backup of the database under the new name and drop the tables you don't want.

I did also find this potential solution using SQL Server Management Studio. You can generate the scripts for the specific tables to move and then export the data using the Generate Scripts Wizard and Import/Export Wizard in SQL Server Management Studio. Then on the new database you would run the scripts to create all of the objects and then import the data. We are probably going to go with the backup/restore method as described in @Joe Stefanelli's answer but I did find this method and wanted to post it for others to see.

To generate the sql script for the objects:

• SQL Server Management Studio > Databases > Database1 > Tasks > Generate Scripts...
• The SQL Server Scripts Wizard will start and you can choose the objects and settings to export into scripts
  • By default the scripting of Indexes and Triggers are not included so make sure to trun these on (and any others that you are interested in).

To export the data from the tables:

• SQL Server Management Studio > Databases > Database1 > Tasks > Export Data...
• Choose the source and destination databases
• Select the tables to export
  • Make sure to check the Identity Insert checkbox for each table so that new identities are not created.

Then create the new database, run the scripts to create all of the objects, and then import the data.