We use Windows Azure around here a lot, it works great with Access but it has one limitation: you can’t copy data from one database to another since they don’t support the USE statement. When there is a will, there is a way, and we’ve got a way you can copy data between Azure databases.
Use Generate Script Instead
To get around this limitation use the following technique:
- Launch SQL Server Management Studio and login to your database
- Right click on your database name and click on Generate Scripts
- Select Choose Objects on the left hand side menu
- Click on Select Specific database objects on the right part of the window
- Checkmark the tables you wish to copy
- Click on Set Scripting Options on the left
- Select Save scripts to a specific location and Save to new query window
- Click on the Advanced button as shown below:
When you click on Advanced you will get a list of options, go down to the bottom of the list and select either Data, Schema and Data or Schema only for Types of data to script:
Here’s an explanation of the three choices:
- Data Only – Will create a script that will insert all of your data into the destination table. Choose this option to copy data to an exact copy of the table. (Both tables have the dame fields)
- Schema and Data – Will create your table and then insert it
- Schema Only – Will only create the table, but not insert any data
Click on OK and click on Next until your script is created. The system will create a new query window, change the USE statement at the top and copy/paste in a new query window of your destination database. Run the script there and it will insert all of your data.
Too much data?
This works great if you are not inserting a lot of data, otherwise it may create a script that is too long to run.
Are there any alternatives considering the scenario in which one of my tables has over 31,000,000 rows of data?
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/
Well if it helps anyone, I had the following scenario.
7 tables in database A needed to be moved to database B because i had a horrible experience in cross database queries on azure using external tables…
Using the method suggested here, it took 50 minutes to insert 102,500 rows for a young database
So i could imagine you would be in a bit of trouble if you had millions of rows.
It would probably work but could take hours.
Hello. I’ve tried this (copying data only). The script generation fails – are there any tricks to this? For example I have some saved files (blobs); does this trip things up?
Also (I’m newish to SQL Server) – having inserted rows in the destination DB – do I need to reset the key generation sequences somehow?