迁移到Azure SQL(BCP) [英] Migrating to Azure SQL (BCP)

问题描述

我正在尝试将数据库从本地SQL服务器(SQL 2008 R2)迁移到Azure SQL安装.

I am attempting to migrate a database from my local SQL server (SQL 2008 R2) to an Azure SQL installation.

使用我在网络上找到的迁移工具，它突出显示了Azure中某些功能不可用的问题，主要问题是批量插入.

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.

此后，我已经通过调用xp_cmdshell以使用BCP替换了此功能.

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

我现在收到xp_cmdshell无法使用天蓝色的错误！

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

当前版本的Azure SQL数据库不支持xp_cmdshell"

"xp_cmdshell is not supported in current version of Azure SQL Database"

有人知道SQL Server 2008 R2和Azure SQL中都可用的解决方法或另一种方法来批量导入数据吗?

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.

推荐答案

您可以在Azure中使用批量插入.首先，您必须为下面的blob创建一个外部数据源

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  
    );

现在您可以像下面那样使用BULKINSERT

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(将在未来几个月内)支持一个SQLAZURE托管实例(当前正在预览)，就像带有SQLAGent，CLR和东西的OnPremises SQLServer一样

Azure (in coming months) supports a SQLAZURE managed instance(currently in preview now),this is same like OnPremises SQLServer with SQLAGent,CLR and stuff

查看全文