Migration to Azure SQL (BCP)
I am trying to migrate a database from my local SQL server (SQL 2008 R2) to an Azure SQL installation.
Using a migration tool I found on the internet, he highlighted some issues where features are not available in Azure, the main one being BULK INSERT.
I have since replaced this function with a call to xp_cmdshell to use BCP.
Now I am getting an error that xp_cmdshell is not available in blue!
DECLARE @BCPString VARCHAR(255) = 'BCP ' + @DatabaseName + '.dbo.TEMP IN ' + @Path + ' -c -t "," -r "0x0a" -S ' + @ServerName + ' -T'
EXEC xp_cmdshell @BCPString, no_output
"xp_cmdshell is not supported in the current version of Azure SQL Database
Does anyone know of a workaround or other method for bulk importing data that can be used in both SQL 2008 R2 and Azure SQL?
The import I need to do is simple, it creates the table, imports and then processes the rest, after which the table drops.
source to share
you can use Bulk Insert to Azure. First, you need to create an external datasource for blob as shown below.
CREATE EXTERNAL DATA SOURCE MyAzureInvoicesContainer
WITH (
TYPE = BLOB_STORAGE,
LOCATION = 'https://newinvoices.blob.core.windows.net/week3',
CREDENTIAL = UploadInvoices
);
Now you can use BULKINSERT like below
BULK INSERT tablename
FROM 'product.csv'
WITH ( DATA_SOURCE = 'MyAzureBlobStorage',
FORMAT='CSV', CODEPAGE = 65001, --UTF-8 encoding
FIRSTROW=2,
TABLOCK);
Azure (in the coming months) maintains a managed instance of SQLAZURE (currently in preview), this is the same as OnPremises SQLServer with SQLAGent, CLR, etc.
source to share