I was asked to upload some data to the Azure server. I set up the LinkedServer, created tables and started uploading by inserting through the LinkedServer. After a while I mentioned it takes way too long. I started googling the reasons and found out the following answer:
Remote data modifications through a linked server use the sp_cursor model. The effect is similar to issuing 1000 separate single inserts (one for each row). If a round trip takes 250ms, 1000 such trips will take 4 minutes and 10 seconds. Using a bulk loading method such as bcp or SSIS will generally be more efficient (unless the number of rows to be inserted is tiny).
It means Microsoft forces you to use SSIS or other external tools to bulk-insert your data. Well, personally, I don't like to switch tools when it comes to simple operations like bulk-inserts. I haven't manage to find a way to run bulk insert directly from table to table, but you can export csv and import it back to Azure and it works just fine:
exec xp_cmdshell 'bcp "select * from YourLocalTable" queryout C:\CSVFolder\Load.csv -w -T -S .'
exec xp_cmdshell 'bcp YourAzureDBName.dbo.YourAzureTable in C:\CSVFolder\Load.csv -S yourdb.database.windows.net -U youruser@yourdb.database.windows.net -P yourpass -q -w'
I've contributed my workaround to stackoverflow as well. It's not heavily tested and it's a workaround after all. If you have a nicer solution I would be more that happy to have it.