Go to Top

Copy Data Between Two Azure Databases

how to copy data from

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.

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

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:

AdvancedButton

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:

Data Only

 

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.

 

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He’s a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

4 Responses to "Copy Data Between Two Azure Databases"

  • jake
    July 23, 2017 - 8:01 am Reply

    Are there any alternatives considering the scenario in which one of my tables has over 31,000,000 rows of data?

  • Johnson
    January 16, 2017 - 12:36 pm Reply

    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/

  • Just me
    January 4, 2017 - 7:03 pm Reply

    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.

  • Gavin
    November 17, 2015 - 1:05 am Reply

    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?

    Cheers

    Gavin

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
[gravityform id="16" title="false" description="false"]