How to append all tables from one Azure Database to another

azure-sql-databasebulkcopy

I have two Azure databases (on different Azure instances) with the same schema. I need to append all the records from one of the databases to the other. All keys are GUIDs so should not collide.

Many tables have foreign key constraints. I have worked out the dependencies between tables and have the order in which I want the tables to be copied.

What tool allows me to append tables and specify the order in which the tables are copied? Should I use MS Data Migration Assistant? SSIS? Import and Export Wizard? Some other tool?

Best Answer

The easiest way to do it is going to be to SSIS to migrate the data over. The wizards alone should be able to do something this simple. The most efficient way would be to export everything to Azure blob storage and then use BULK INSERT. I'd do it all using Powershell so that you can control error handling better. It's going to be a lot more work though. If it's not that big and it's a one-time thing, I'd go with SSIS.

Related Solutions

Sql-server – Move data from one table to other in batch’s

while (1=1)
begin
    delete top(10000) from oldtable
       output deleted.columnA, deleted.columnB, deleted.columnC
       into newtable (columnA, columnB, columnC)
    if @@ROW_COUNT = 0
       break;
end

Sql-server – SQL Server 2012 import/export wizard not showing at all

The import/export wizard is not showing because it is not available for Azure SQL. Even if you downloaded the latest version of SSMS, you shouldn't see it.

You are in a different environment that how on-premise SQL Server databases work. In that Azure SQL does not support using cross database queries. So doing an insert into or select insert are not going to work.

Depending on how big the table is, your easiest thing to do is using the script object wizard and just include the data. Then just run that under the destination database.

If you need to do this on a routine basis then look at the data sync services Azure offers.

Best Answer

Related Solutions

Sql-server – Move data from one table to other in batch’s

Sql-server – SQL Server 2012 import/export wizard not showing at all

Related Question