How to Import CSV in SQL Server

Galaxy Glossary

How do I import a CSV into SQL Server using BULK INSERT?

BULK INSERT quickly loads data from a CSV file into an existing SQL Server table.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why choose BULK INSERT over manual INSERT?

BULK INSERT streams the entire file in one operation, bypassing row-by-row overhead. It dramatically reduces import time, especially for large ecommerce datasets like millions of Orders.

What prerequisites must be met?

Ensure the target table exists, the SQL Server service account can read the file path, and the file uses a consistent delimiter—typically comma-separated with CRLF line endings.

How do I write the BULK INSERT command?

Specify table, file path, delimiter, row terminator, first row, and optional error file. Keep options readable by formatting each on a new line.

Syntax explained option-by-option

DATAFILETYPE = 'char' treats data as character strings, FIELDTERMINATOR sets the column separator, and ROWTERMINATOR defines line breaks. Use FIRSTROW = 2 to skip headers.

How do I import customers from a CSV?

Place customers.csv on the SQL Server, grant READ permissions, and run the example query in the next section. Verify the row count afterward.

Best practices for reliable imports

Always import into a staging table matching the CSV structure. Validate and cleanse, then merge into production tables using INSERT ... SELECT with key deduplication.

How to handle errors gracefully?

Use ERRORFILE = 'C:\logs\customers_err.log' to capture rejected rows. After import, inspect the log, correct data, and rerun only failed rows.

Why How to Import CSV in SQL Server is important

How to Import CSV in SQL Server Example Usage


-- Load a CSV of new customers
BULK INSERT dbo.Customers
FROM 'C:\data\customers.csv'
WITH (
    DATAFILETYPE    = 'char',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR   = '\n',
    FIRSTROW        = 2,
    TABLOCK,
    ERRORFILE       = 'C:\logs\customers_err.log'
);
-- Verify the import
SELECT COUNT(*) AS total_customers FROM dbo.Customers;

How to Import CSV in SQL Server Syntax


BULK INSERT dbo.Customers
FROM 'C:\data\customers.csv'
WITH (
    DATAFILETYPE    = 'char',          -- read as text
    FIELDTERMINATOR = ',',             -- comma-separated values
    ROWTERMINATOR   = '\n',           -- line break (\r\n on Windows)
    FIRSTROW        = 2,               -- skip CSV header
    TABLOCK,                           -- bulk lock for speed
    ERRORFILE       = 'C:\logs\customers_err.log'
);

Common Mistakes

Using a network path (\\server\share) without enabling SQL Server to access the share. Fix: copy the file locally or grant the SQL Server service account read permissions.
Leaving the CSV header row in data by omitting FIRSTROW=2. Fix: add FIRSTROW=2 or remove the header before import.

Frequently Asked Questions (FAQs)

Can I import a CSV with different column order?

Create a staging table that matches the CSV order, import the data, then INSERT...SELECT into the production table with explicit column mapping.

How do I handle NULL values?

Use KEEPNULLS in the WITH clause to treat empty strings as NULL when appropriate.

Is BULK INSERT faster than SSIS?

For straightforward file-to-table loads, BULK INSERT is comparable or faster due to lower overhead. SSIS excels when complex transformations are required.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie