In this post I present a step by step method to copy tables, with data, from one database to another database inside a single SQL Server 2005 Express Edition.
More information about SQL Server 2005 Express Edition can be found here.
STEP 1: (right click on table name)
Create a script for the schema of the table to be copied. This is the table with all the data.
After you click create, you will see a code window as shown below.
Change the database name in the script to the new database name (the database where you are going to copy this table). This is done just once by changing the text in the code as shown below.
Now click on execute to run the SQL command as shown below.
You will see a command window as shown below at the end of the execution. If you see an error your table copy failed. If the copy fails please make sure you have followed the instructions above properly, and you are not missing any opening/closing square brackets.
After STEP 5 you can go ahead and refresh the database where the table was copied, and you will see an entry for the table. If you open the table you will see there is no data in it. To copy the data from the original table please follow the following steps.
Create a SELECT command by clicking the "SELECT To" as shown below,
STEP 7: (right click on old table)
Copy the SELECT query on this window to clipboard. We will use in in STEP 11.
STEP 8: (right click on old table)
Create an Insert query as shown below,
Delete the portion of the query starting with VALUES, as shown below.
Change the database name in the INSERT query to the "new" database name as shown below,
Paste the SELECT query command from STEP 7 to the Insert Query window as shown below.
Execute the command as shown below.
Now you can go to your new database and refresh the table to see the data in there. If you know of a better method of achieving this result, please leave a comment below. Thanks.