| Lesson 4 | The BULK INSERT statement |
| Objective | Use the BULK INSERT statement in SQL Server 2025 |
BULK INSERT loads a large file straight into a table with a single T-SQL statement. In modern
environments, that file is usually a CSV or other delimited export sitting on local disk, a network share, or
cloud storage that SQL Server has been configured to reach. The objective of this lesson is to understand the
core BULK INSERT workflow, the options that control parsing and batching, and the operational and
security constraints that determine whether an import actually succeeds.
BULK INSERT is the right tool when you want a repeatable script, predictable performance, and
explicit control over delimiters, row handling, and error tolerance, rather than an interactive import wizard.
The previous two lessons covered INSERT and SELECT INTO, both of which are plain T-SQL
and work well for smaller result sets built from existing tables. BULK INSERT picks up where those
leave off: loading a file, often a large one, directly from outside the database. For more elaborate pipelines
you may also reach for OPENROWSET(BULK...), SSIS, Fabric/Data Factory, or an external ETL tool, but
BULK INSERT is still the foundational option worth mastering first, and the one you'll reach for most
often when the source is a single well-formed file.
Before you run BULK INSERT, validate these practical requirements. Most import failures are not
syntax problems; they are file-access, permissions, or format mismatches.
CREATE EXTERNAL DATA SOURCE statement pointing at the
container, secured with either a database-scoped credential built from a shared access signature token, or, on
SQL Server 2025 and Azure SQL, a managed identity instead. BULK INSERT then references the file by
its relative path within that data source, not by pasting the full blob URL with its SAS token into the
FROM clause.
ADMINISTER BULK OPERATIONS permission, or membership in
the bulkadmin server role, already introduced earlier in this module when this course covered
permissions for data-loading operations.
For production workflows, a staging table is often preferable. It lets you load quickly, then validate and
transform data before merging into the final schema, the same load-then-validate pattern this course has already
recommended for both INSERT and SELECT INTO.
CREATE TABLE dbo.YourTargetTable
(
Column1 int NOT NULL,
Column2 nvarchar(100) NOT NULL,
Column3 datetime2(0) NULL
);
datetime2 is generally preferred over the legacy datetime type for its extra
precision and clearer rounding behavior.The SQL Server service account must be able to read the file. This matters most with UNC paths and hardened environments where file-system permissions are locked down tightly.
C:\imports\yourfile.csv\\fileserver\imports\yourfile.csvThis example loads a CSV file, skips a header row, and declares delimiters explicitly rather than relying on defaults, which avoids ambiguity when the file comes from a tool you don't fully control.
BULK INSERT dbo.YourTargetTable
FROM 'C:\imports\yourfile.csv'
WITH
(
FORMAT = 'CSV',
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '0x0a',
TABLOCK
);
FIRSTROW = 2 is the common way to skip a header row from a CSV export. It's worth knowing that
SQL Server still reads that first row to check it against the field terminators; the row isn't simply
discarded unread, so a malformed header can still raise an error.ROWTERMINATOR = '0x0a', a hex line feed, tends to be more reliable than a plain '\n'
across tools that disagree about exactly how a newline is encoded.TABLOCK acquires a table-level lock for the duration of the load, which usually improves
throughput for a load with no concurrent readers or writers, subject to your recovery model and concurrency
needs.A common pattern is: load, validate, transform, merge. If you load directly into the final table, you still need a validation step to catch data issues early, before anything downstream depends on the new rows.
SELECT COUNT(*) AS rows_loaded
FROM dbo.YourTargetTable;
SELECT TOP (50) *
FROM dbo.YourTargetTable
ORDER BY Column1;
Because BULK INSERT skips constraint checks and triggers by default, covered in more detail below,
any primary key, foreign key, or check constraint the target table needs should be added, or re-enabled, after
the load rather than assumed to already be enforcing itself during it.
To see the whole workflow together, consider loading a CSV export into the Inventory table this
course built earlier, with its StockNum, Cost, RetailPrice,
QtyAvailable, and LastPriceChangeDate columns. If the CSV's columns line up with the
table's columns in the same order, a plain BULK INSERT is enough:
BULK INSERT dbo.Inventory
FROM 'C:\imports\inventory_update.csv'
WITH
(
FORMAT = 'CSV',
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '0x0a',
MAXERRORS = 5,
ERRORFILE = 'C:\imports\inventory_update_errors'
);
MAXERRORS = 5 tolerates up to five bad rows before canceling the whole load, and
ERRORFILE captures the rejected rows, exactly as submitted, to a file for review afterward, rather
than losing them entirely the moment the row fails. If the source file's columns don't match
Inventory's layout, perhaps an external system exports the fields in a different order, a format
file, covered later in this lesson, maps one to the other explicitly.
The most important BULK INSERT options are the ones that control parsing, batching, and error
behavior. These are where you tune for speed and operational resilience.
FIRSTROW: skips header or metadata rows at the top of the file.FIELDTERMINATOR: defines the delimiter between fields, a comma, tab, pipe, or anything
else the file uses.ROWTERMINATOR: defines line endings explicitly. In mixed environments, a hex terminator
reduces surprises.MAXERRORS: sets a tolerance threshold, 10 bad rows by default, before the load is
canceled.ERRORFILE: captures rejected rows to a file, as submitted, for review and remediation.KEEPNULLS: preserves NULLs from the file instead of letting a column's default constraint
fill them in.KEEPIDENTITY: keeps the identity values already present in the file instead of having SQL
Server generate new ones, the same kind of override SET IDENTITY_INSERT provides for an ordinary
INSERT, already covered two lessons ago.FIRE_TRIGGERS and CHECK_CONSTRAINTS: both off by default. A plain
BULK INSERT skips triggers and constraint checks for speed, which is exactly why a load that depends
on either one has to ask for it explicitly rather than assuming it's happening automatically.TABLOCK: acquires a table-level lock for better throughput, with the usual awareness of
concurrency impact that any locking hint carries.BATCHSIZE: commits data in batches, each batch in its own transaction, improving
recoverability and reducing log pressure on a very large load.ROWS_PER_BATCH: provides an estimate to help SQL Server optimize internal resources for the
load.
In performance-tuning terms, BATCHSIZE is typically the most operationally significant knob, because
it affects transaction log behavior, lock durations, and how much work is lost if something fails mid-load.
When using BULK INSERT, batching options influence how data is grouped and committed:
ROWS_PER_BATCH: provides SQL Server an estimate for how many rows are in the file, which can
help with memory grants and internal planning. Depending on file size and settings, SQL Server may still treat
the load as a single operation; this is only a planning hint, not a commit boundary.
BATCHSIZE: commits the load in discrete transactions. If a load fails, only the current batch
rolls back, which is often preferable for large files and operational stability. Without BATCHSIZE,
the entire file is a single batch and a single transaction, so a failure partway through a very large load rolls
back everything loaded so far, not just the rows that actually caused the problem.
If you specify both ROWS_PER_BATCH and BATCHSIZE, the commit behavior is governed by
BATCHSIZE; use ROWS_PER_BATCH as a planning hint and BATCHSIZE whenever you
actually need transaction control.
If you use an ORDER option, remember that sorting and comparisons are evaluated using the database or
column collation. If the file isn't actually sorted according to that collation, or if the destination table or
index doesn't support the requested ordering, SQL Server simply ignores the hint rather than raising an
error.[1]
Logging is the other factor worth understanding before loading a genuinely large file.
BULK INSERT is one of the operations this course's discussion of recovery models already flagged as
minimally logged under the simple or bulk-logged recovery model, meaning SQL Server records far less in the
transaction log than it would for an ordinary row-by-row INSERT. Under the full recovery model, the
load is fully logged instead, which is safer for point-in-time restore but noticeably slower for a very large
file. Choosing which recovery model a database runs under, covered earlier in this module, is part of the same
decision as choosing how to bulk-load data into it.
When you need more control than "the file's columns match the table's columns," use a format file. A format file explicitly maps fields in the incoming file to columns in the destination table, and can also describe field terminators, fixed-width fields, and data type expectations.
A format file is useful when:
In practice, format files are generated and maintained using the bcp utility, run with its
format option. Once you have a stable format file, it becomes a durable contract between whoever
produces the file and your database load process, reusable across every future load of a file with the same
layout, rather than something rebuilt by hand each time.
A non-XML format file starts with two header lines: the bcp version that created it, and the number
of fields described. Every field after that gets one row with eight parts, in this order: the field's position in
the source file, its data type as the file stores it (text files typically use SQLCHAR for every
field and let SQL Server convert on load), a prefix length, the field's maximum length, its terminator, the
column's position in the destination table, where 0 skips that field entirely, the destination
column's name, and its collation. Here is one built for a CSV loading into this course's own
Inventory table:
14.0
5
1 SQLCHAR 0 11 "," 1 StockNum ""
2 SQLCHAR 0 12 "," 2 Cost ""
3 SQLCHAR 0 12 "," 3 RetailPrice ""
4 SQLCHAR 0 11 "," 4 QtyAvailable ""
5 SQLCHAR 0 23 "\r\n" 5 LastPriceChangeDate ""
Reading through it field by field:
bcp release that created the file. A bcp reading the
file back must be the same version or a later one.SQLNCHAR,
SQLINT, SQLBIT, and SQLDATETIME, among others, depending on how the file
itself stores the data.Inventory table. Setting this to 0 for a field skips it entirely rather than loading it
anywhere.
To skip a field from the data file entirely, set its server column order to 0 and leave the prefix
length and terminator settings intact so the loader can still advance correctly through the rest of the row.
The next lesson covers the bcp utility itself, which both generates format files like this one and
provides a command-line alternative to BULK INSERT for moving data into and out of SQL Server.
Click the Quiz link below to verify that you can identify the best loading approach for a given scenario and interpret the key BULK INSERT options.