Move data between two Azure SQL databases without using elastic query

Question

I am in need of suggestion to move data from a particular table in one azure sql database to the other azure sql database which has the same table structure without using elastic query

Answer 1

In the source database I created SPs to select the data from the tables. In the target database I created table types (which would be available in programmability) for the tables with the same structure as in the source. I used Azure function to move the data into table type from source. In the target database I created SPs to insert data into the tables from their respective table types. After ensuring the transfer of data, I would be deleting those records moved to the target in the source database and for this I created SPs.

Answer 2

Using SQL Server Management Studio to connect to SQL azure database, right click the source database and select generate scripts.

During the wizard, after have select the tables that you want to output to a query window, then click advanced. About half way down the properties window there is an option for "type of data to script". Select that and change it to "data only", then finish the wizard.

The heck the script, rearrange the inserts for constraints, and change the using at the top to run it against my target DB.

Then right click on the target database and select new query, copy the script into it, and run it.

This will migrate the data.

Answer 3

You can use PowerShell to query each database and move data between them as needed. Here's an example article on how to get this done.

Using PowerShell when working with Azure has a number of other benefits in what you can do and can control as well. It's a good choice to spend time learning.

Answer 4

Please consider using the "Transfer SQL Server Objects task" in SSIS. You can learn all the advantages it provides on this article.