bulk-load-and-bulk-copy

Load rows fast: BULK INSERT from Blob Storage, bcp, and the driver bulk APIs

Moves rows into a table that already exists, and kills the two habits carried over from SQL Server: a local file path, and a minimal-logging tune-up. Designing that table is another job.

Engine behaviour measured 2026-08-29 against a container reporting SERVERPROPERTY('EngineEdition') 5 and 12.0.2000.8, with bcp 18.6.0002.1 from mssql-tools18. Every option and flag below comes from Microsoft Learn's BULK INSERT, bcp utility, sys.dm_user_db_resource_governance and "Resource management in Azure SQL Database" pages, fetched 2026-09-03.

The correction

A local path is refused before the file is opened. BULK INSERT and OPENROWSET(BULK ...) both fail on a filesystem path with:

Msg 12713, Level 16, State 1, Server SQL Azure, Line 1
OPENROWSET is not allowed to read local files. Path: '/tmp/data.csv'.

The message names OPENROWSET even for a BULK INSERT, which is built on the same machinery. A path that was never created produces the identical message, so this is a platform rule evaluated first, not a failed open. Learn's platform table agrees: on Azure SQL Database the only data source is Azure Storage, authenticated by Microsoft Entra ID, a SAS token or a managed identity. * wildcards in the path and FORMAT = 'PARQUET' are not supported there either.

Minimal logging is not there to tune. Learn, same page: "Minimal logging isn't supported in Azure SQL Database." What caps ingestion is the transaction log rate governor, a per-service-tier limit on log record generation enforced at the subsecond level, "limiting throughput regardless of how many IOs can be issued against data files". It surfaces as a LOG_RATE_GOVERNOR wait, or POOL_LOG_RATE_GOVERNOR in an elastic pool. TABLOCK and BATCHSIZE do not raise that ceiling, and were measured not to move the Msg 12713 refusal either. The documented mitigations are to scale up or change tier, stage transient data in tempdb, or load into a clustered columnstore or compressed table.

Before quoting an error number, a wait type or a throughput figure, open references/bulk-load-errors-and-log-rate-governor.md.

Choose the route

What you have	Route
A file already in Azure Blob Storage	BULK INSERT or OPENROWSET(BULK ...) with DATA_SOURCE. The only case where the server reads the file
A file on a client machine	bcp, which streams from the client, so the Blob-only rule never applies
Rows in a running application	that language's bulk API. Never write a file just to load it back
A whole database, not rows for one table	sqlpackage. Wrong route, right command

BULK INSERT from Blob Storage

Managed identity leaves no secret to leak. Grant the database's identity the Storage Blob Data Contributor role on the container first:

CREATE DATABASE SCOPED CREDENTIAL BlobCred WITH IDENTITY = 'Managed Identity';

CREATE EXTERNAL DATA SOURCE MyBlob WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://<storage-account>.blob.core.windows.net/<container>',
    CREDENTIAL = BlobCred);

BULK INSERT dbo.MyTable
FROM 'load/data.csv'
WITH (DATA_SOURCE = 'MyBlob', FORMAT = 'CSV', FIRSTROW = 2,
      FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a', MAXERRORS = 0,
      ERRORFILE = 'load/rejects', ERRORFILE_DATA_SOURCE = 'MyBlob');

For a shared access signature instead, the credential needs a database master key behind it, and the rest of the statement is unchanged:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong-password>';

CREATE DATABASE SCOPED CREDENTIAL BlobCred
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '<sas-token>';

Five details decide whether it runs:

• The path in FROM is relative to LOCATION, not a second copy of it.
• MAXERRORS defaults to 10, so an unset load discards nine bad rows and still reports success.
• ERRORFILE needs ERRORFILE_DATA_SOURCE beside it here or the import can fail with a permissions error, and the named file must not already exist in the container.
• The SAS token carries no leading ?, needs at least srt=o&sp=r, and all its dates are UTC.
• The caller needs INSERT and ADMINISTER DATABASE BULK OPERATIONS. ADMINISTER BULK OPERATIONS, without DATABASE, is the SQL Server spelling.

FIRSTROW is not a header skip: Learn says BULK INSERT does not support skipping headers, and skipped rows are scanned for field terminators without being validated.

An ad hoc https:// URL with no credential object of that name fails with Msg 15151 naming the whole URL, which means no such credential exists, not that one was denied. OPENROWSET(BULK 'load/data.csv', DATA_SOURCE = 'MyBlob', SINGLE_CLOB) takes the same data source and fails through the same layer, so the two count as one route.

bcp, from a client machine

bcp ships inside the engine container image, not on the host, so run it there:

/opt/mssql-tools18/bin/bcp dbo.MyTable in data.csv \
  -S <server>.database.windows.net -d <database> -G \
  -c -t',' -r'\n' -F 2 -b 5000 -m 0 -e bcp-rejects.txt

-G is Microsoft Entra authentication, the supported route to Azure SQL Database; -T (Windows integrated) is not supported against it. -c is character mode; -t and -r override the tab and newline defaults; -F 2 skips the header row that BULK INSERT cannot; -b makes each batch its own committed transaction, so a failure late in the file keeps the batches already loaded; -m 0 tolerates no conversion error (also 10 by default); -e is the only way to see the rejected rows. The -h "TABLOCK" hint is Windows only, so it does not exist in the container.

Certificate trust was measured on 2026-08-29 as failing silently: -u, -Yo and an ODBC DSN carrying TrustServerCertificate=yes each exited 1 with zero bytes on stdout and stderr, while the paths that refuse the certificate returned a readable SSL or TCP error. Never promise bcp will "just work" once a trust flag is added.

The client library bulk APIs

These stream rows over the bulk-copy protocol on the connection the application already holds, so Msg 12713 cannot apply: none of them reads a path on the server.

.NET, Microsoft.Data.SqlClient.SqlBulkCopy:

dotnet add package Microsoft.Data.SqlClient
dotnet run

using var bulk = new SqlBulkCopy(connection);
bulk.DestinationTableName = "dbo.MyTable";
bulk.BatchSize = 5000;          // 0, the default, is one transaction for the entire load
bulk.BulkCopyTimeout = 0;       // per batch, not per operation
bulk.EnableStreaming = true;
bulk.ColumnMappings.Add("csv_id", "Id");   // required whenever names or order differ
await bulk.WriteToServerAsync(reader);

Python, pyodbc. fast_executemany bundles every parameter set into one ODBC array and has no batch size: the driver allocates one contiguous buffer for all remaining rows, so a million-row list is a single allocation. Chunk it yourself.

cursor.fast_executemany = True
statement = "INSERT INTO dbo.MyTable (Id, Name) VALUES (?, ?)"
for start in range(0, len(rows), 5000):
    cursor.executemany(statement, rows[start:start + 5000])
    cursor.commit()

Node.js, the mssql package. Every column has to state its nullability explicitly:

const table = new sql.Table('dbo.MyTable');
table.create = false;
table.columns.add('Id', sql.Int, { nullable: false });
table.columns.add('Name', sql.VarChar(50), { nullable: true });
for (const r of rows) table.rows.add(r.id, r.name);
const rowCount = await new sql.Request().bulk(table);

None of the three was exercised live, so before tuning past the properties shown, open references/driver-bulk-apis.md.

Not a bulk load: a whole database

If every table's schema and rows are moving, rather than rows into one existing table, this is sqlpackage-import-export, not a bulk load:

sqlpackage /Action:Import /SourceFile:app.bacpac \
  /TargetServerName:<server>.database.windows.net /TargetDatabaseName:<database> \
  /TargetUser:<user> /TargetPassword:"$SQLPACKAGE_PASSWORD"

Check it worked

Exit code 0, or a "rows copied" line, says the client finished, not that the rows landed.

sqlcmd -S <server>.database.windows.net -d <database> -G \
  -Q "SELECT COUNT_BIG(*) AS loaded FROM dbo.MyTable;"

Expect the file's row count minus its header. Short means rows were rejected, not lost: they are in the ERRORFILE or the -e file, and at the default of 10 the load still reports success.

Then find the ceiling it worked against, before promising a number next time. This runs in the cloud only:

SELECT database_name, primary_max_log_rate / 1048576.0 AS max_log_MiB_per_sec
FROM sys.dm_user_db_resource_governance;

SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_db_wait_stats
WHERE wait_type LIKE '%LOG_RATE_GOVERNOR%';

primary_max_log_rate is that tier's maximum log generation rate in bytes per second. A non-zero wait_time_ms on LOG_RATE_GOVERNOR is the load being throttled by it, and the answer is a higher service level or less log volume, not a bigger batch.

On the local container the first query fails with Msg 208, invalid object name, and there is no substitute. Measured 2026-09-03: sys.dm_user_db_resource_governance and sys.dm_db_resource_stats are both absent, and sys.dm_internal_resource_governor_resource_pools exists but returns zero rows. The log rate governor is a service behaviour and it is not observable locally, so do not size a load against a number the container gave you. The wait statistics query does run in both places.

Do not

• Do not write a local or UNC path into either statement. Msg 12713, file or not.
• Do not offer TABLOCK, a bigger BATCHSIZE or a recovery model change for a slow load.
• Do not read Msg 15151 as a permissions error. No credential object of that name exists.
• Do not leave MAXERRORS or -m at 10 and call the load clean.
• Do not write application data out to a file so it can be loaded back in.

References

• Before quoting Msg 12713, 15151 or 4861 or a wait type, open references/bulk-load-errors-and-log-rate-governor.md, which carries the exact text of each plus the platform limits table.
• BULK INSERT (Transact-SQL) and bcp utility: fetch before using an option or flag not shown here, because both say which platforms accept which.
• Resource management in Azure SQL Database: the per-tier and Hyperscale log rate numbers, before promising a throughput.
• connect-from-python, connect-from-dotnet, connect-from-typescript-and-node for getting connected, which this skill assumes is done. diagnose-resource-pressure when a whole workload is slow rather than one load hitting the governor.