共享数据库服务器上BULK INSERT的高性能替代方案寻求

针对共享云SQL Server环境下无法获取ADMINISTER BULK OPERATIONS服务器级权限的情况，以下是几个仅需数据库级权限的高性能方案：

1. 使用OPENROWSET(BULK...) + 数据库级批量权限 #

从SQL Server 2017开始，支持数据库级的ADMINISTER DATABASE BULK OPERATIONS权限，只需在目标数据库执行授权：

GRANT ADMINISTER DATABASE BULK OPERATIONS TO [YourUser];

之后就可以用OPENROWSET直接读取文件并批量插入/更新，速度接近BULK INSERT：

-- 示例：批量插入到目标表
INSERT INTO TargetTable (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM OPENROWSET(
    BULK 'C:\DataFiles\UpdateData.csv',
    FORMATFILE = 'C:\DataFiles\FormatFile.xml', -- 可选，用于定义列格式
    FIRSTROW = 2 -- 跳过表头
) AS BulkData;

如果是更新操作，可以结合MERGE语句：

MERGE INTO TargetTable AS T
USING (
    SELECT Col1, Col2, Col3
    FROM OPENROWSET(BULK 'C:\DataFiles\UpdateData.csv', FIRSTROW = 2) AS BulkData
) AS S
ON T.PrimaryKey = S.PrimaryKey
WHEN MATCHED THEN
    UPDATE SET T.Col2 = S.Col2, T.Col3 = S.Col3
WHEN NOT MATCHED THEN
    INSERT (Col1, Col2, Col3) VALUES (S.Col1, S.Col2, S.Col3);

注意：文件路径需要是SQL Server服务账户可访问的位置（云环境中可能需要上传到云存储映射的路径）。

2. 使用SqlBulkCopy（客户端批量加载） #

如果你的应用是.NET开发的，SqlBulkCopy是最优选择之一，仅需目标表的INSERT权限，性能几乎和BULK INSERT持平。它通过客户端将数据批量发送到服务器，避免服务器级权限要求：

using (SqlConnection conn = new SqlConnection("YourConnectionString"))
{
    conn.Open();
    // 假设已经从文件读取到DataTable或IDataReader
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
    {
        bulkCopy.DestinationTableName = "TargetTable";
        // 映射列（如果列名不一致）
        bulkCopy.ColumnMappings.Add("SourceCol1", "TargetCol1");
        bulkCopy.ColumnMappings.Add("SourceCol2", "TargetCol2");
        
        // 设置批量大小，根据内存调整，一般1000-10000最佳
        bulkCopy.BatchSize = 5000;
        // 启用批量超时
        bulkCopy.BulkCopyTimeout = 300;
        
        // 执行批量复制
        bulkCopy.WriteToServer(dataTable);
    }
}

如果需要更新而非单纯插入，可以先批量导入到临时表，再用MERGE同步到目标表：

-- 先创建临时表
CREATE TABLE #TempTable (Col1 INT, Col2 VARCHAR(50), Col3 DATETIME);
-- 用SqlBulkCopy把数据写入#TempTable
-- 再执行MERGE
MERGE INTO TargetTable AS T
USING #TempTable AS S
ON T.Col1 = S.Col1
WHEN MATCHED THEN UPDATE SET T.Col2 = S.Col2, T.Col3 = S.Col3
WHEN NOT MATCHED THEN INSERT (Col1, Col2, Col3) VALUES (S.Col1, S.Col2, S.Col3);

DROP TABLE #TempTable;

3. 使用批量INSERT语句（分批次） #

如果无法使用上述工具，可将数据拆分为多个批量INSERT语句，每批次插入1000-10000条记录，比逐行插入快数十倍：

-- 示例：每批次插入1000条
INSERT INTO TargetTable (Col1, Col2, Col3)
VALUES
(1, 'Val1', '2024-01-01'),
(2, 'Val2', '2024-01-02'),
-- ... 共1000条记录
(1000, 'Val1000', '2024-04-09');

可以用脚本或工具（如Python的pandas）将CSV文件转换为这种批量INSERT格式，避免逐行提交的开销。

4. 使用云存储集成（针对云环境） #

如果是Azure SQL Database，可将数据文件上传到Azure Blob存储，然后用OPENROWSET读取，只需数据库级权限：

INSERT INTO TargetTable (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM OPENROWSET(
    BULK 'https://yourstorage.blob.core.windows.net/container/UpdateData.csv',
    FORMAT = 'CSV',
    FIRSTROW = 2,
    CREDENTIAL = 'AzureStorageCredential' -- 数据库级的存储凭据
) AS BulkData;

存储凭据可在数据库内创建，无需服务器级权限：

CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'your-sas-token';

内容的提问来源于stack exchange，提问作者Michael