user1948635 user1948635 -4 years ago 169
SQL Question

Migrating to Azure SQL (BCP)

I am attempting 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 web, it has highlighted some issues where features are unavailable in Azure, the main one being BULK INSERT.

I have since replaced this feature with a call to xp_cmdshell to use BCP.

I am now getting an error that xp_cmdshell is unavailable in azure!

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 current version of Azure SQL Database"

Does anyone know of a workaround or another method to bulk import data which is usable in both SQL 2008 R2 and Azure SQL?

The import I need to perform is simple, it creates a table, imports, then the procedure deals with the rest with the table being dropped afterwards.

Answer Source

you can use Bulk insert in Azure.First you have to create an external data source to a blob like 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 coming months) supports a SQLAZURE managed instance(currently in preview now),this is same like OnPremises SQLServer with SQLAGent,CLR and stuff

Microsoft/sql-server-samples

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download