BULK INSERT and OPENROWSET are both T-SQL mechanisms for reading data from files outside SQL Server. The confusion between them is understandable — both can move rows from a CSV into a table, and the documentation for each references the other. But they were designed for opposite ends of a spectrum: BULK INSERT is optimized for throughput, and OPENROWSET is optimized for flexibility.
Choosing based on use case eliminates most of the performance and permission problems that arise when the wrong one is reached for.
Full comparison at a glance
| Dimension | BULK INSERT | OPENROWSET |
|---|---|---|
| Primary purpose | Load file data into an existing table as fast as possible | Query file data as a virtual table — select, join, filter, transform |
| Throughput | Maximum — supports minimal logging, TABLOCK, BATCHSIZE | Moderate — full logging, no bulk optimization flags |
| Output | Writes rows directly into a destination table | Returns a rowset — can be read by SELECT, INSERT INTO, MERGE |
| Pre-existing destination table required | Yes — table must exist and match the file schema | No — can be used with SELECT INTO to create table from results |
| Filtering during load | No — all rows land in the table; filtering happens after | Yes — WHERE, JOIN, GROUP BY can be applied in the query |
| Transformation during load | No — values arrive as-is from the file | Yes — expressions, CAST, CONVERT, ISNULL all apply |
| Minimal logging | Yes — with TABLOCK + BULK-LOGGED recovery model | No — always fully logged |
| Ad Hoc Distributed Queries setting | Not required | Required — must be enabled by a server administrator |
| Permissions required | ADMINISTER BULK OPERATIONS or BULKADMIN server role | ADMINISTER BULK OPERATIONS + Ad Hoc Distributed Queries enabled |
| Works on Azure SQL | Yes — with Blob Storage URL and DATABASE SCOPED CREDENTIAL | OPENROWSET(BULK) works; ACE.OLEDB provider does not |
| Format file support | Yes — non-XML and XML format files | Yes — same format file support |
| Error file support | Yes — ERRORFILE redirects bad rows | No — errors abort the query |
BULK INSERT: What It Does Well and Where It Falls Short
BULK INSERT is a DML statement that streams file data directly into a named table. Its performance advantage over every other T-SQL file-reading approach comes from two capabilities: minimal logging and table-level locking. When configured correctly — TABLOCK hint, BULK-LOGGED recovery model, destination is a heap or empty table — SQL Server can load millions of rows without writing each one individually to the transaction log. The minimal logging guide covers the exact conditions required.
The trade-off for that speed is rigidity. BULK INSERT maps file columns to table columns positionally or by format file — it has no query engine between the file and the table. You cannot write a WHERE clause that loads only rows where the sale amount exceeds 100. You cannot JOIN the incoming file against an existing customer table to validate IDs before they land. Every row in the file goes into the table, and any transformation or filtering must happen afterward with a separate UPDATE or DELETE pass. This is why the staging table pattern — load everything into an unconstrained staging table with BULK INSERT, then use a MERGE statement to selectively promote rows to production — is the professional pattern for high-volume imports where data quality is uncertain.
When to choose BULK INSERT
The performance gap between BULK INSERT and OPENROWSET widens with file size. At 10 million rows, BULK INSERT with minimal logging configured finishes in minutes; OPENROWSET at the same scale is measured in hours. For anything that runs on a schedule and involves large files, BULK INSERT is the only practical choice.
BULK INSERT is easy to embed in a stored procedure or a PowerShell script via Invoke-Sqlcmd. It has no configuration requirements beyond file access permissions and the ADMINISTER BULK OPERATIONS privilege. For daily feeds from a fixed source with a stable column layout, it is the simplest path to production.
Because BULK INSERT supports minimal logging, it is the only file-loading approach in SQL Server that can be configured to avoid growing the transaction log in proportion to the number of rows imported. For very large files on a server with limited log disk space, OPENROWSET is not viable.
OPENROWSET: Two Distinct Modes
OPENROWSET is not a single function — it has two modes that work completely differently, and the documentation for both lives under the same function name. Understanding which mode applies to CSV files prevents a lot of confusion.
| Mode | First argument | What it does | When to use |
|---|---|---|---|
| BULK mode | BULK 'filepath' | Reads a file as a rowset using the same engine as BULK INSERT — but returns results into a query instead of directly into a table. Supports the same WITH clause options as BULK INSERT. | Inspect a file, transform on the fly, or use as the source in INSERT INTO … SELECT or MERGE |
| Provider mode | 'Microsoft.ACE.OLEDB.12.0' | Connects to an OLE DB provider installed on the SQL Server machine — ACE.OLEDB for Excel/Access/CSV, or other providers. Treats the external file like a linked server table. | Reading Excel files (.xlsx), querying CSVs with named headers through the ACE driver, legacy integration scenarios |
For CSV imports, OPENROWSET(BULK ...) is the relevant mode. It accepts the same WITH clause options as BULK INSERT — FORMAT, FIRSTROW, FIELDTERMINATOR, ROWTERMINATOR — but wraps the result in a rowset that SQL Server's query engine can operate on. The ACE.OLEDB provider mode is useful for Excel files but is rarely the right choice for plain CSV data.
When to choose OPENROWSET
OPENROWSET(BULK) with SINGLE_CLOB reads the entire file as a single text value. OPENROWSET with a full column definition lets you SELECT * from the file to see its contents without creating a table. This is useful for auditing an unknown file, checking row counts, or verifying column values before writing the destination table DDL.
Because OPENROWSET returns a queryable rowset, you can add a WHERE clause to the SELECT. For example: only import rows where the Status column equals "Active," or only import price updates for products that already exist in the Products table (using a JOIN). This filtering is impossible with a pure BULK INSERT approach, which must load everything first.
OPENROWSET as the source in a MERGE statement is a concise way to implement an upsert from a CSV file. The MERGE statement compares the incoming file data against the production table on a key column — matching rows trigger UPDATE, non-matching rows trigger INSERT — all in a single query without a staging table. This works well for small-to-medium files where the full logging overhead of OPENROWSET is acceptable.
OPENROWSET inside an INSERT INTO … SELECT allows you to apply CAST, CONVERT, ISNULL, LTRIM/RTRIM, and any other scalar function to each column as it moves from the file into the table. The column-by-column transformation happens inside the query — no second pass required. For large files this is slower than loading raw into staging and transforming afterward, but for smaller files the single-query approach is simpler.
The Permission Difference
Both commands require file system access from the SQL Server service account — not from your Windows login. If the CSV is on your laptop and the SQL Server is on a different machine, neither command will see the file. The file must be on a local drive of the SQL Server itself, on a UNC network share the service account can read, or — for Azure SQL — in Azure Blob Storage with a scoped credential. The BULK INSERT troubleshooting guide covers the service account permission setup in detail.
OPENROWSET has an additional requirement: Ad Hoc Distributed Queries must be enabled on the server. This is a SQL Server surface area configuration setting that is disabled by default because it allows queries to reach external data sources, which is a potential security surface. Enabling it requires sysadmin rights and a server-level configuration change.
Permission requirements by command
| Requirement | BULK INSERT | OPENROWSET |
|---|---|---|
| SQL Server login permission | ADMINISTER BULK OPERATIONS (server role: bulkadmin), or INSERT on the destination table | ADMINISTER BULK OPERATIONS + sysadmin to enable Ad Hoc Distributed Queries |
| Ad Hoc Distributed Queries setting | Not required | sp_configure 'Ad Hoc Distributed Queries', 1 — must be run as sysadmin |
| File system access | SQL Server service account needs Read on the file/folder | Same — SQL Server service account, not the login's Windows account |
| ACE.OLEDB provider (for provider mode) | Not applicable | Required for Excel/Access files — must be installed on the SQL Server machine. Not needed for BULK mode. |
| Azure SQL | DATABASE SCOPED CREDENTIAL with SAS token pointing to Blob Storage | OPENROWSET(BULK) with same credential; ACE.OLEDB provider not available on Azure SQL |
Ad Hoc Distributed Queries is a security surface area setting — not a trivial toggle
Enabling Ad Hoc Distributed Queries allows any user with sufficient SQL Server permissions to query external data sources — not just CSV files, but also remote SQL Server instances, OLE DB providers, and other linked data. On shared or multi-tenant servers, enabling this setting warrants a security review. On a dedicated data engineering server you control, the risk is lower but still worth documenting. Many organizations allow it only in development and disable it in production, relying on BULK INSERT in automated pipelines instead.
Combining Both: The Staging + Merge Pattern
The two commands are not mutually exclusive. A common production pattern uses both in sequence: BULK INSERT loads the raw file into an unconstrained staging table at maximum speed, and then OPENROWSET — or more commonly, a plain T-SQL MERGE targeting the staging table — handles the transformation and selective promotion to the production table.
BULK INSERT → staging table
All rows from the CSV land in a staging table with no constraints, no indexes, and all NVARCHAR(MAX) columns. TABLOCK and BATCHSIZE are configured for maximum throughput. This step is fast regardless of file size.
Validate and transform in staging
T-SQL UPDATE and DELETE statements run against the staging table to fix formatting, cast types, remove duplicates, and flag invalid rows. This happens inside the database with full query engine capabilities — no file parsing overhead.
MERGE staging → production
A MERGE statement compares staging against the production table on the primary key. Matching rows trigger UPDATE; non-matching rows trigger INSERT. Rows in production not present in staging can optionally be deleted with a WHEN NOT MATCHED BY SOURCE clause. The result is a consistent production table that reflects the incoming file.
This pattern gives you the throughput of BULK INSERT for the file-reading step and the full flexibility of T-SQL for the data quality and promotion step — without the performance penalty of using OPENROWSET to load millions of rows. For the MERGE syntax and the staging table design, see the schema evolution and staging guide.
Decision summary
BULK INSERTRegular loads, large files, performance-critical pipelines, Azure SQL with Blob StorageOPENROWSET(BULK)Inspect unknown files, filtered imports, upserts on small-to-medium files, one-time transformationsBULK INSERT → MERGELarge files with transformation, deduplication, or upsert requirements — best of bothOPENROWSET (ACE.OLEDB)Reading Excel or Access files — not a CSV tool; requires the ACE provider installed server-sideFurther reading
Optimal method selected automatically
Elvity selects BULK INSERT or MERGE based on file size, schema, and whether the destination table has existing rows — without any configuration on your part.