Tuesday, April 17, 2007

Copy Data between SQL Express Database(s)

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.

STEP 10:

Change the database name in the INSERT query to the "new" database name as shown below,

STEP 11:

Paste the SELECT query command from STEP 7 to the Insert Query window as shown below.

STEP 12:

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.


Unknown said...

I will use these instructions so do not delete these.

You may have to help me in some practical simulation, SCM and DOE. Will that be possible or I will have to pay for that.

Jyotirmaya said...

Please send me an email with details.

azuriaf said...

thank you so much for this entry..

Unknown said...

fantastic post! This is so chock full of users information and the resources you
provided was helpful to me. This is comprehensive and helpful list. There I found
informative blog explaining Copy Data From One Server To Another In SQL Server which I Found interesting:http://www.sqlmvp.org/copy-data-from-one-server-to-another-server/

(c) Jyotirmaya Nanda 2012