Standard INSERT statements process rows one at a time — each row is parsed, validated, logged, and written individually. At 500 rows per second, a 10-million-row file takes over five hours. BULK INSERT works differently: it streams data directly from a flat file into the table's data pages, bypassing much of the row-by-row overhead. With the right configuration, the same 10-million-row file lands in under 30 minutes.
This guide covers the core syntax and the five conditions that produce peak performance. For the deep explanation of why these conditions work at the engine level, see the companion minimal logging guide.
Prerequisites
Two things must be true before a BULK INSERT command can run successfully — and both are commonly overlooked.
When BULK INSERT runs, it is not your Windows user account reading the file — it is the SQL Server service account (typically NT Service\MSSQLSERVER). That account needs Read permission on the folder where the CSV is stored. If you can see the file but BULK INSERT reports "Access Denied," this is the cause. Right-click the folder → Security → Edit → Add the service account and grant Read. Alternatively, place the file on a local drive on the SQL Server itself.
BULK INSERT does not create tables. The destination table must already exist, and its columns must align with the CSV structure. If the table has five columns and the CSV has six, the import fails. If column order doesn't match, data lands in the wrong columns. Use a staging table with all NVARCHAR(MAX) columns if you're unsure of the exact schema — you can fix types after the data is loaded.
The Core Syntax: Every Option Explained
BULK INSERT is a single T-SQL statement with a WITH clause that controls how the file is parsed and how the import behaves. The options below are the ones you'll use in every real-world import.
BULK INSERT WITH clause options
| Option | What it does | Typical value |
|---|---|---|
| FORMAT | Sets the file format. CSV activates RFC 4180 parsing (quoted fields respected) | 'CSV' |
| FIRSTROW | Row number where data begins. Use 2 to skip a header row | 2 |
| FIELDTERMINATOR | Character that separates columns. Redundant when FORMAT = CSV but explicit is safer | ',' |
| ROWTERMINATOR | Character(s) that end a row. Use hex values to avoid ambiguity | '0x0d0a' (Windows) or '0x0a' (Linux) |
| TABLOCK | Acquires a table-level lock — required for minimal logging. No value needed | (no value — flag only) |
| BATCHSIZE | Commits every N rows as a separate transaction. Limits log growth and rollback scope | 50000 to 100000 |
| ROWS_PER_BATCH | Hint to the optimizer about expected total rows — helps memory allocation | Total rows ÷ expected batches |
| ERRORFILE | Path where rejected rows are written instead of aborting the import | 'C:\Logs\errors.log' |
| MAXERRORS | Maximum number of errors before the entire import aborts | 0 (strict) or 100 (tolerant) |
| KEEPIDENTITY | Preserve ID values from the CSV in an IDENTITY column instead of generating new ones | (no value — flag only) |
| KEEPNULLS | Insert NULL for empty fields instead of the column default value | (no value — flag only) |
| DATAFILETYPE | Encoding of the file. Use widechar for Unicode/UTF-16 | 'char' or 'widechar' |
FORMAT = 'CSV' is SQL Server 2017+ only
The FORMAT = 'CSV' option — which activates RFC 4180 parsing and respects double-quoted fields containing commas — was introduced in SQL Server 2017. On 2016 and earlier, you must specify FIELDTERMINATOR and ROWTERMINATOR explicitly, and commas inside quoted fields will be misinterpreted as delimiters. The fix for 2016 and earlier is to change the source file's delimiter to pipe or tab.
The Performance Workflow
Running BULK INSERT with default settings is faster than row-by-row INSERT, but not by much — SQL Server still logs every row individually. The real speed gain comes from enabling minimal logging, which requires five conditions to be true simultaneously. Miss one and you're back to full row-by-row logging.
Switch to BULK-LOGGED recovery (temporarily)
Minimal logging is impossible in FULL recovery mode. Before the import, switch the database to BULK-LOGGED: ALTER DATABASE YourDatabase SET RECOVERY BULK_LOGGED. This preserves the log backup chain while allowing bulk operations to skip row-level logging. Switch back to FULL and take a log backup immediately after the import completes.
Drop non-clustered indexes on the target table
Every non-clustered index must be updated for every inserted row — in real time, during the load. A table with three indexes means three index updates per row, on top of the base write. Drop them before the load with DROP INDEX, run BULK INSERT, then rebuild with CREATE INDEX. Rebuilding one index in a single sorted pass is dramatically faster than updating it incrementally for every batch.
Add TABLOCK to the WITH clause
TABLOCK acquires an exclusive table-level lock for the duration of the import. This signals SQL Server that no other process can read or write the table concurrently — which is the precondition for minimal logging. Without TABLOCK, SQL Server defaults to row-level locking and logs every single row regardless of the recovery model.
Set BATCHSIZE to 50,000–100,000
Without BATCHSIZE, the entire CSV is one atomic transaction. A failure at row 49,999,999 of a 50-million-row file rolls back everything. BATCHSIZE commits every N rows as its own transaction — a failure affects only the current batch, and the transaction log can be truncated between batches. The 50k–100k range balances commit overhead against log growth.
Load into a heap or empty table
Minimal logging works most reliably when loading into a heap (a table with no clustered index) or into a completely empty table. Loading into a populated table with a clustered index forces SQL Server to sort incoming rows into the existing B-Tree structure — which is expensive and often prevents minimal logging even when all other conditions are met. This is why the staging table approach — load into an unconstrained heap, validate, then merge into production — is the professional pattern for high-volume imports.
After a successful import: rebuild indexes, switch back to FULL recovery, and take a log backup. The staging table + MERGE pattern for moving data from staging to production is covered in the schema evolution guide.
Handling "Dirty" Data: Error Files
Real-world CSV files contain bad rows — a text value where a number belongs, a date in the wrong format, a null in a non-nullable column. By default, a single bad row aborts the entire import and rolls back everything processed so far. The ERRORFILE and MAXERRORS options change this behavior: bad rows are skipped and written to a log file, and the import continues with the valid rows.
| Option | Behavior | Recommended value |
|---|---|---|
| ERRORFILE | Each rejected row is written to this file with an explanation | A path the SQL service account can write to |
| MAXERRORS = 0 | Any error aborts the import (strict mode) | Production pipelines with clean source data |
| MAXERRORS = N | Import continues until N errors accumulate, then aborts | 100–1000 for exploratory loads from unknown sources |
ERRORFILE generates two files
When you specify ERRORFILE = 'C:\Logs\errors.log', SQL Server creates two files: errors.log (the rejected row data) and errors.log.Error.Txt (the reason each row was rejected). Read the .Error.Txt file first — it identifies the error type (type mismatch, truncation, constraint violation) which tells you exactly what to fix in the source data.
Common Errors and Fixes
Fix: The SQL Server service account (not your Windows login) needs Read permission on the folder. Find the service account in Windows Services → SQL Server → Properties → Log On tab. Add it to the folder's Security settings with Read access. Alternatively, copy the file to a local drive on the SQL Server where the service account always has access.
Full troubleshooting guide →Fix: A CSV value is longer than the column definition allows. Find the offending column by loading the data into a staging table with all NVARCHAR(MAX) columns first, then run SELECT MAX(LEN(column_name)) on each column to find the actual maximum lengths. Set production column widths accordingly before the real import.
Fix: A non-numeric or badly formatted date value exists in a column declared as numeric or date. Use ERRORFILE to capture the failing rows and inspect the actual values. For dates, add SET DATEFORMAT dmy before the command if your dates are in day-first format. For "N/A" style strings, import as NVARCHAR in staging and filter them out before moving to production.
Fix: In T-SQL string literals, backslashes are not escape characters — the path C:\Data\file.csv is valid. The issue usually appears when paths are pasted from Windows Explorer into a query window and include special characters. On Linux-based SQL Server (or Docker), use forward slashes: /mnt/data/file.csv.
Fix: A comma inside a quoted field was treated as a delimiter. On SQL Server 2017+, use FORMAT = 'CSV' which enables RFC 4180 quote-aware parsing. On SQL Server 2016 and earlier, change the source file's delimiter to pipe or tab and update FIELDTERMINATOR to match.
When BULK INSERT Is the Wrong Tool
BULK INSERT is a server-side command — the file path must be accessible from the SQL Server, not from your local machine. If you're connecting to a remote or cloud SQL Server instance from your laptop, the file must be on the server itself or on a network share the server can reach. The remote transfer guide covers the file transfer step.
For files where the schema needs to be inferred, columns need to be transformed before landing, or data needs to be validated against existing records before insertion, BULK INSERT into a staging table is still the right first step — but the validation and transformation logic lives in T-SQL after the load, not during it. The primary keys and MERGE guide covers the pattern for moving validated data from staging into a constrained production table.
Performance in one sentence
BULK-LOGGED recovery + TABLOCK + heap target + BATCHSIZE + no non-clustered indexes — all five conditions simultaneously produce minimal logging. Any one of them missing reverts to full row-by-row logging with no error or warning. This is why a BULK INSERT that's fast in a dev database is slow in production: the recovery model is different.
In this series
Skip the T-SQL, keep the performance
Elvity generates and executes the optimal BULK INSERT pipeline for your schema automatically — TABLOCK, BATCHSIZE, recovery model switching, and index management included.