Importing/Exporting Data  «Prev  Next»

Lesson 4 The BULK INSERT statement
Objective Use the BULK INSERT statement in SQL Server 2025

Using BULK INSERT 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.


Prerequisites and operational requirements

Before you run BULK INSERT, validate these practical requirements. Most import failures are not syntax problems; they are file-access, permissions, or format mismatches.

  1. A target table that matches the incoming data
    • Column order and data types must align with the file, or you must use a format file, covered later in this lesson, to map fields that don't line up directly.
    • Plan for NULL handling, date and time formats, and decimal separators ahead of time rather than discovering a mismatch mid-load.
  2. SQL Server must be able to access the file
    • On-premises or VM: the file must be on a local path that the SQL Server service account can read, or on a network share reachable by a UNC path, with appropriate permissions.
    • Azure Blob Storage: this requires a 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.
  3. Security and least privilege
    • The account running the load needs the 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.
    • Grant only the minimal permissions required to read the file location. Avoid embedding long-lived secrets in scripts; prefer scoped credentials, managed identities, or short-lived tokens instead.
    • Use private networking controls where feasible: private endpoints, restricted firewall rules, and audited access all reduce the blast radius if a credential is ever compromised.
  4. Decide how you will handle failures
    • Do you want the load to stop on the first error, or tolerate a limited number of bad rows?
    • Do you need a reject file or error file for remediation afterward?
    • Will you load into a staging table first, then validate and transform into the final table, or load directly into production?



Step-by-step: a practical BULK INSERT workflow

  1. Create a target table, or a staging table

    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.
  2. Confirm file accessibility from the SQL Server service context

    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.

    • Local path example: C:\imports\yourfile.csv
    • UNC path example: \\fileserver\imports\yourfile.csv
  3. Execute BULK INSERT with explicit CSV options

    This 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.
  4. Validate results and apply constraints after the load

    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.


Loading the course's own Inventory table

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.


Additional options that matter in real imports

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.


Notes about batching, ordering, and logging

When using BULK INSERT, batching options influence how data is grouped and committed:

  1. 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.
  2. 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.


Specifying a format file

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:

  1. The file contains a different number of fields than the destination table.
  2. The file's field order differs from the table's column order.
  3. Delimiters vary, or the file mixes fixed-width and delimited segments.

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.


Bulk insert statement in SQL Server - Format file

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  ""
A non-XML format file mapping a five-field CSV file to the Inventory table's five columns.

Reading through it field by field:

  1. 14.0: identifies the bcp release that created the file. A bcp reading the file back must be the same version or a later one.
  2. 5: the number of fields described below, one row per field.
  3. 1 through 5 (leftmost column): the order each field appears in the source file.
  4. SQLCHAR: the host file's data type for that field. Other values include SQLNCHAR, SQLINT, SQLBIT, and SQLDATETIME, among others, depending on how the file itself stores the data.
  5. 0: the prefix length. Valid values are 0, 1, 2, 4, or 8; a prefix is required when a field can hold a NULL value.
  6. 11, 12, 12, 11, 23: each field's maximum length in bytes.
  7. "," and "\r\n": the terminator for each field, a comma between fields and a carriage-return/line-feed pair ending the row.
  8. 1 through 5 (server column order): the destination column's ordinal position in the Inventory table. Setting this to 0 for a field skips it entirely rather than loading it anywhere.
  9. StockNum, Cost, RetailPrice, QtyAvailable, LastPriceChangeDate: the destination column name for each field, for readability. The trailing empty quotes are the collation field, left blank here because none of these columns hold character data that needs one specified.

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.

Loading Data - Quiz

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.

Loading Data - Quiz


[1]Collation sequence: Determines the order in which Unicode character data is compared and sorted.

SEMrush Software 4 SEMrush Banner 4