SQL Server - copy table data from one server to a table in an Azure SQL Database

Question

I'm migrating a database from Azure VM to Azure SQL Database. I tried to use the "Deploy Database to Azure SQL Database" function in SSMS but it failed several times, seemingly due to the size of the database (110 GB). So I made a copy of the source database on the same source server, truncated the table with the majority of the data in it, then tried the deploy again. Success.

Now I need to get that data from the original source table into the destination table. I've tried two different approaches to this and both gave errors

1. In SSMS, I connected to both SQL Servers. I ran the below while attached to the destination database:

      INSERT INTO dbo.DestinationTable
      SELECT *
      FROM [SourceServer].[SourceDatabase].dbo.SourceTable
   

With that I was given the error:

Reference to database and/or server name in 'SourceServer.SourceDatabase.dbo.SourceTable' is not supported in this version of SQL Server.

2. In SSMS, used the Export Data Wizard from the Source Table. When trying to start that job, I received this error during the validation phase:

Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "CaptureId"

How can I accomplish what should be this seemingly simple task?

Answer 1

instead of using deploy database to azure directly for large databases ,you could try below steps

1.Extract bacpac on local machine
2.Copy bacpac to blob
3.Now while creating database, you could use the bacpac in blob as source

This approach worked for us and is very fast,you may also have to ensure,that blob is in same region as SQLAzure

Answer 2

I've solved the issue. Using option #2, you simply need to tick the checkbox for "Enable Identity Insertion" and it works with no errors. This checkbox is inside the Edit Mappings sub-menu of the Export Data Wizard.