How to Import CSV Into SQL Server

SQL Server documents several ways to move a CSV into a table. The Import Flat File Wizard copies a file "to a new table in your database" from inside SQL Server Management Studio. BULK INSERT reads a path "from the server on which SQL Server is running", or a UNC share, and asks for the ADMINISTER BULK OPERATIONS permission. The bcp utility runs on a machine somebody signs into. Each one starts from a seat that already holds a server connection. Your customer sits outside that seat, holding the survey export a consultancy sent them this morning.

The door above a thousand rows

Microsoft publishes a ladder for writing many rows at once, and it has three rungs. "For < 100 rows, use a single parameterized INSERT command." "For < 1,000 rows, use table-valued parameters." "For >= 1000 rows, use SqlBulkCopy."

The bottom rung runs out on its own. An INSERT ... VALUES list stops at a thousand rows, and Microsoft numbers the failure, since "Error 10738 is returned if the number of rows exceeds the maximum". A city register with several thousand buildings in it starts on the top rung.

Bulk copy carries a set of rows straight over the connection. There is no statement to parse, no parameter to count, and no row limit to work around. In Node that door is request.bulk(), in the mssql package.

It comes with one restriction, and Microsoft states it plainly. Other techniques, "such as SQL bulk copy, only permit the insertion of new rows." So the rows land in a staging table first, and one statement afterwards decides what each of them becomes. Below a thousand rows the middle rung is the shorter road, and importing a CSV into Azure SQL Database takes it.

The file that arrives

A city conservation office keeps a register of listed buildings. The consultancy that ran this season's survey sends one export.

1Ref,Site Address,Listing Grade,First Recorded,Date Surveyed,Conditon,Floor Area (sqm),Statutory2HA-0142,ul. Świętego Tomasza 24,II*,11/04/1698,03/04/2026,Good,"1,240.50",Y3HA-0143,Kanonicza 7,Grade 2,29/09/1802,17/11/2025,fair,487.456,Y4HA-0144,Grodzka 53,Local list,02/05/1936,03/04/2026,at-risk,96,N5HA-0142,ul. Świętego Tomasza 24,II,11/04/1698,03/04/2026,POOR,"1,240.50",Yes⋮313 rows not shown319HA-0459,Floriańska 41,Grade 2,17/06/1874,22/05/2026,Good,612.30,Y

The header row says Ref where the register says reference, and Conditon because somebody typed it that way. Grades arrive as II*, Grade 2, II and Local list. Dates run day first across three centuries. HA-0142 appears twice, once Good and once POOR, because the surveyor revisited and left both lines in.

The person drags the file into the importer inside your app. Updog Importer reads it in the browser, matches the headers to your schema, holds the grades to your lists, and puts every row in front of them. Your onComplete handler posts the clean rows to a route you own. That route streams them into a staging table and calls one procedure.

No Updog server stands between the browser and the database.

The table and the one in front of it

Two tables hold the work. The register is the destination, and the staging table is the landing strip bulk copy needs.

CREATE TABLE dbo.heritage_asset (
    reference       NVARCHAR(20)  NOT NULL PRIMARY KEY,
    address         NVARCHAR(160) NOT NULL,
    grade           NVARCHAR(10)  NOT NULL,
    first_recorded  DATE          NULL,
    surveyed_on     DATE          NOT NULL,
    asset_condition NVARCHAR(20)  NOT NULL,
    floor_area_sqm  DECIMAL(9, 2) NULL,
    listed          BIT           NOT NULL,
    updated_at      DATETIME2(0)  NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE dbo.heritage_asset_stage (
    batch_id        UNIQUEIDENTIFIER NOT NULL,
    reference       NVARCHAR(20)  NOT NULL,
    address         NVARCHAR(160) NOT NULL,
    grade           NVARCHAR(10)  NOT NULL,
    first_recorded  DATE          NULL,
    surveyed_on     DATE          NOT NULL,
    asset_condition NVARCHAR(20)  NOT NULL,
    floor_area_sqm  DECIMAL(9, 2) NULL,
    listed          BIT           NOT NULL,
    op              NVARCHAR(6)   NOT NULL,
    INDEX IX_stage_batch NONCLUSTERED (batch_id)
);

The staging table repeats every declaration and adds two columns. op carries what the person did to the row, and batch_id keeps one import apart from another, since a permanent staging table can hold two people's chunks at the same time.

Three of those declarations were chosen against the file rather than by habit.

address is NVARCHAR because the second field of the first row reads ul. Świętego Tomasza 24. Under char and varchar, "If a non-UTF-8 collation is specified, then these data types store only a subset of characters supported by the corresponding code page of that collation." Microsoft's own advice is a UTF-8 enabled collation on SQL Server 2019 and later, or nchar and nvarchar before that.

first_recorded is DATE because 11/04/1698 is 55 years below the floor of datetime, which runs "1753-01-01 (January 1, 1753) through 9999-12-31 (December 31, 9999)". Microsoft says the rest out loud. "Avoid using datetime for new work. Instead, use the time, date, datetime2, and datetimeoffset data types."

floor_area_sqm is DECIMAL(9, 2), which keeps seven digits in front of the point and two behind it.

What the declaration decides

Each declaration decides how a value that does not fit will die, and SQL Server has two ways of doing it.

One of them is loud. A string longer than its column raises an error carrying the table, the column and the value, since SQL Server 2019 phrases it as "String or binary data would be truncated in table '%.*ls', column '%.*ls'. Truncated value: '%.*ls'." That is error 2628 under database compatibility level 150, where VERBOSE_TRUNCATION_WARNINGS defaults to ON. Turn the setting off at the same level and the same failure arrives as the older error 8152, which names nothing.

Two of them are quiet. A character outside the collation's code page is replaced on the way in, and Microsoft states the consequence in one sentence. "As with earlier versions of SQL Server, data loss during code page translations isn't reported." A third decimal digit is dropped the same way, since "By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale", and "Loss of only precision and scale isn't sufficient to raise an error".

So 487.456 becomes 487.46 and nothing says so. Only the person who measured that building knows whether it matters. A browser-side importer is the last place they are still in the room.

The schema in Updog Importer

The columns array carries the same widths and lists the tables declared.

import type { DataEditorColumn } from "@updog/data-editor";

const GRADES = ["Grade I", "Grade II*", "Grade II", "Local"];
const CONDITIONS = ["Good", "Fair", "Poor", "At risk"];
const YES_NO = ["Yes", "No"];

export const columns: DataEditorColumn[] = [
  {
    id: "reference",
    title: "Reference",
    size: 130,
    transformer: (value) => String(value).trim().toUpperCase(),
    validators: [
      { type: "required" },
      { type: "regex", pattern: "^HA-\\d{4}$" },
      { type: "unique" },
    ],
  },
  {
    id: "address",
    title: "Address",
    size: 260,
    validators: [
      { type: "required" },
      { type: "function", fn: (value) => String(value).length > 160
        ? { level: "error", message: "160 characters at most" }
        : null },
    ],
  },
  {
    id: "grade",
    title: "Grade",
    size: 130,
    editor: { type: "select", options: GRADES, enableCustomValue: false },
    validators: [{ type: "required" }, { type: "oneOf", values: GRADES }],
  },
  {
    id: "firstRecorded",
    title: "First recorded",
    size: 150,
    editor: { type: "date" },
    validators: [{ type: "date" }],
  },
  {
    id: "surveyedOn",
    title: "Survey date",
    size: 150,
    editor: { type: "date" },
    validators: [
      { type: "required" },
      { type: "date" },
    ],
  },
  {
    id: "condition",
    title: "Condition",
    size: 130,
    editor: { type: "select", options: CONDITIONS, enableCustomValue: false },
    validators: [{ type: "required" }, { type: "oneOf", values: CONDITIONS }],
  },
  {
    id: "floorAreaSqm",
    title: "Floor area",
    size: 140,
    editor: { type: "number" },
    formatter: (value) => (value ? value + " m2" : ""),
    validators: [
      { type: "number", min: 0, max: 9_999_999, decimalPlaces: 2 },
    ],
  },
  {
    id: "listed",
    title: "Statutory listing",
    size: 160,
    editor: { type: "select", options: YES_NO, enableCustomValue: false },
    validators: [{ type: "required" }, { type: "oneOf", values: YES_NO }],
  },
];

Each rule answers a line in the table definition. The regex holds the reference inside NVARCHAR(20) and inside the register's own format. The function validator counts the address against 160 characters, so truncation is reported in a grid cell. decimalPlaces: 2 holds the floor area to two decimal digits. The select editors hold the grade and the condition to fixed lists, and a value nobody maps is dropped.

{ type: "unique" } on the reference is the rule that saves the write. Uniqueness is checked against every other row in the same column, so both HA-0142 cells light up before submit. That matters because the merge refuses the pair, where "The MERGE statement can't update the same row more than once, or update and delete the same row."

What the person reads and what travels

A grid of bare numbers reads badly, and a database column has no room for the unit. The formatter settles that split.

It formats the display value while the stored data stays as it was. The person reads 1240.5 m2, the store holds 1240.5, and the store reaches DECIMAL(9, 2). Search runs on the formatted text, so somebody typing the unit still finds the cell. Find and replace writes back to the stored value.

The transformer works from the other end. It runs as rows are uploaded, so ha-0142 arrives trimmed and upper case, ahead of the pattern check.

The headers the consultancy sends

Updog scores each header against the column id and the column title, and the higher score wins.

Conditon is the interesting one. The contains tier refuses at eight characters against nine, and no whole word is shared. One edit sits inside the three allowed at that length, so the typo lands anyway. Ref is three characters, below the floor of every tier, and no built-in group carries it. It needs the one synonyms entry in the mount below.

Values follow the same ladder. at-risk normalizes to atrisk and equals the option. Local list contains Local. Grade 2 shares one word of two with Grade II and lands at seventy. Y and N reach Yes and No at ninety, since the built-in table already carries them. II and II* are too short to reach anything, so the mount spells them out.

The file settles its own dates. 29/09/1802 and 17/11/2025 both carry a value above twelve in the second position, so the scan reads the whole file day first, and 11/04/1698 and 03/04/2026 follow.

The mount

The props tie the file, the schema and the register together.

<DataEditor<HeritageAsset>
  apiKey="your-license-key"
  open={open}
  onClose={closeEditor}
  columns={columns}
  primaryKey="reference"
  enableDeleteRow="all"
  blockSubmitOnError
  synonyms={{
    columns: {
      reference: ["ref", "asset ref", "uprn"],
    },
    values: {
      "Grade II*": ["ii*", "2*", "grade 2*"],
      "Grade II": ["ii", "grade 2"],
      "Grade I": ["i", "grade 1"],
      Local: ["local list", "locally listed"],
    },
  }}
  onComplete={onComplete}
/>

primaryKey is the reference alone, which is also the table's primary key and the ON clause of the merge. One column carries the identity of a building through all three.

enableDeleteRow="all" lets the person drop the stale HA-0142 line by hand once the grid shows both. blockSubmitOnError keeps submit disabled while any row carries an error, the duplicate reference included. Whatever they fix by hand comes back on the result as learnedSynonyms, ready to store and feed through synonyms next season.

Every snippet here is React. Those props reach Vue, Angular and Svelte through the web component build. The install and the modal wiring beneath this mount live in how to import a CSV file into a React app.

The result on submit

On submit, Updog Importer hands your handler every row grouped by source, each one carrying four independent flags.

import type { DataEditorResult, ResultRow } from "@updog/data-editor";

const CHUNK_SIZE = 5000;

const toRow = (entry: ResultRow<HeritageAsset>) => {
  if (entry.isDeleted && entry.isNew) return [];
  return [{
    reference: entry.row.reference,
    address: entry.row.address,
    grade: entry.row.grade,
    firstRecorded: entry.row.firstRecorded || null,
    surveyedOn: entry.row.surveyedOn,
    assetCondition: entry.row.condition,
    floorAreaSqm: entry.row.floorAreaSqm ? Number(entry.row.floorAreaSqm) : null,
    listed: entry.row.listed === "Yes",
    op: entry.isDeleted ? "delete" : "upsert",
  }];
};

const onComplete = useCallback(async (result: DataEditorResult<HeritageAsset>) => {
  for (const source of result.sources) {
    const rows = source.rows.flatMap(toRow);

    for (let start = 0; start < rows.length; start += CHUNK_SIZE) {
      const answer = await fetch("/api/heritage/write", {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify({ rows: rows.slice(start, start + CHUNK_SIZE) }),
      });
      if (!answer.ok) throw new Error((await answer.json()).message);
    }
  }
  await storeSynonyms(result.learnedSynonyms);
}, []);

A new building and an edited building become the same payload, since one MERGE decides which of the two it is. A row the person added and then deleted goes nowhere. The names change shape here on purpose, since the staging table calls the column asset_condition.

Every value leaves in the shape its declared type accepts. The floor area becomes a number, the statutory flag becomes a boolean for BIT, and an empty first-recorded date becomes null.

The chunk size is yours. Bulk copy publishes no row cap, and Microsoft's own batching tests report that "there was typically no advantage to breaking large batches into smaller chunks. In fact, this subdivision often resulted in slower performance than submitting a single large batch." So five thousand rows is a number chosen against what your own endpoint accepts, and the ladder is the only published figure in this section.

Throw when a route answers with a failure. Updog holds submit until your promise resolves, then empties the editor. A handler that traps the error and returns counts as a finished import, and the whole survey clears the grid unwritten. A thrown error keeps the grid as it stands, with every mapping and hand correction on it. The person submits again on rows that never left the screen.

The route that streams the rows

The route holds the connection string, and the browser stops there.

import sql from "mssql";
import { randomUUID } from "node:crypto";

const pool = await sql.connect(process.env.SQL_CONNECTION_STRING);

const toTable = (rows, batchId) => {
  const table = new sql.Table("dbo.heritage_asset_stage");
  table.columns.add("batch_id", sql.UniqueIdentifier, { nullable: false });
  table.columns.add("reference", sql.NVarChar(20), { nullable: false });
  table.columns.add("address", sql.NVarChar(160), { nullable: false });
  table.columns.add("grade", sql.NVarChar(10), { nullable: false });
  table.columns.add("first_recorded", sql.Date, { nullable: true });
  table.columns.add("surveyed_on", sql.Date, { nullable: false });
  table.columns.add("asset_condition", sql.NVarChar(20), { nullable: false });
  table.columns.add("floor_area_sqm", sql.Decimal(9, 2), { nullable: true });
  table.columns.add("listed", sql.Bit, { nullable: false });
  table.columns.add("op", sql.NVarChar(6), { nullable: false });

  for (const row of rows) {
    table.rows.add(
      batchId,
      row.reference,
      row.address,
      row.grade,
      row.firstRecorded,
      row.surveyedOn,
      row.assetCondition,
      row.floorAreaSqm,
      row.listed,
      row.op,
    );
  }
  return table;
};

app.post("/api/heritage/write", async (request, response) => {
  const session = await getVerifiedSession(request);
  if (!session) return response.status(401).json({ message: "Not signed in" });
  if (!session.canEditRegister) {
    return response.status(403).json({ message: "No register access" });
  }

  const batchId = randomUUID();

  try {
    await pool.request().bulk(toTable(request.body.rows, batchId));

    const answer = await pool
      .request()
      .input("batch", sql.UniqueIdentifier, batchId)
      .execute("dbo.usp_MergeHeritageBatch");

    response.json(answer.recordset[0]);
  } catch (error) {
    request.log.error({ number: error.number, message: error.message });
    await discardBatch(batchId);
    return response.status(502).json({ message: describe(error) });
  }
});

getVerifiedSession() stands in for your own server-side authentication check. The register permission is checked before a row is read. sql.Table names the staging table and then declares each column, in the order the table declares them, with the same widths. A value the declared type cannot take is refused at the driver, so the boundary check happens in one place.

The batch id is minted here and never leaves the server. It is what lets the procedure find this import's rows among anybody else's, and what lets discardBatch clean up after a failure. Microsoft asks for one thing beside it. "Avoid parallel execution of batches that operate on a single table in one database."

The statement that settles the batch

The procedure reads the rows this batch landed and writes the register once.

CREATE PROCEDURE dbo.usp_MergeHeritageBatch
    @batch UNIQUEIDENTIFIER
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @verdicts TABLE (verdict NVARCHAR(10));

    BEGIN TRANSACTION;

    MERGE dbo.heritage_asset WITH (HOLDLOCK) AS target
    USING (SELECT * FROM dbo.heritage_asset_stage
            WHERE batch_id = @batch AND op = N'upsert') AS source
        ON target.reference = source.reference
    WHEN MATCHED THEN
        UPDATE SET address         = source.address,
                   grade           = source.grade,
                   first_recorded  = source.first_recorded,
                   surveyed_on     = source.surveyed_on,
                   asset_condition = source.asset_condition,
                   floor_area_sqm  = source.floor_area_sqm,
                   listed          = source.listed,
                   updated_at      = SYSUTCDATETIME()
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (reference, address, grade, first_recorded, surveyed_on,
                asset_condition, floor_area_sqm, listed)
        VALUES (source.reference, source.address, source.grade,
                source.first_recorded, source.surveyed_on,
                source.asset_condition, source.floor_area_sqm, source.listed)
    OUTPUT $action INTO @verdicts;

    DELETE target
      FROM dbo.heritage_asset AS target
      JOIN dbo.heritage_asset_stage AS source
        ON source.reference = target.reference
     WHERE source.batch_id = @batch AND source.op = N'delete';

    DELETE FROM dbo.heritage_asset_stage WHERE batch_id = @batch;

    COMMIT TRANSACTION;

    SELECT SUM(CASE WHEN verdict = N'INSERT' THEN 1 ELSE 0 END) AS inserted,
           SUM(CASE WHEN verdict = N'UPDATE' THEN 1 ELSE 0 END) AS updated
      FROM @verdicts;
END;

OUTPUT $action counts the verdicts. It is "a column of type nvarchar(10) that returns one of three values for each row", and Microsoft calls the OUTPUT clause "the recommended way to query or count rows affected by a MERGE". Those two numbers travel back through the route to the browser, so the person learns how many buildings were added and how many were revised.

The merge, the deletes and the staging cleanup share one transaction, so a batch either lands whole or leaves nothing behind. XACT_ABORT rolls it back on any error the statement raises. HOLDLOCK is on the target because the same statement inserts buildings that are new and updates buildings that are already there.

Two footnotes belong on any page that shows this statement. The semicolon is required, and a missing one raises error 10713. And Microsoft's own caution stands. "At scale, MERGE might introduce complicated concurrency issues or require advanced troubleshooting. As such, plan to thoroughly test any MERGE statement before deploying to production."

When the write fails

A rejected write arrives in Node as a RequestError. err.number holds the SQL Server error number the driver read off the wire, beside lineNumber, state and procName. One request can produce several errors at once, and the package says where they land. "SQL Server may generate more than one error for one request so you can access preceding errors with err.precedingErrors."

A bulk copy that fails partway leaves its own rows behind, which is why discardBatch deletes them by batch id before the route answers. Log the number, answer the browser with a sentence a conservation officer can act on, and let the handler throw.

All of this happens with the confirm dialog open and a spinner on the button. Anything on the result worth keeping gets copied inside the handler, since the editor drops its rows, its sources, its history and its learned synonyms once the promise resolves.

The parts nobody ships for you

Updog Importer integrates with nobody. There is no SQL Server connector, no destination list, no webhook and no server of ours. onComplete hands your code an object, and the route, the staging table and the procedure in the middle are work you do.

SQL Server already ships its own way in for the other case. BULK INSERT reads a file the server itself can reach, and bcp runs on a machine somebody signs into. The Import Flat File Wizard copies a flat file "to a new table in your database" once SQL Server Management Studio is installed. It previews "the first 50 rows", types the columns from "the first 200 rows", and encodes with "the system's active code page. On most machines this defaults to ANSI." For a file your own team assembled, those are the shorter way in. Everything above exists for the export a consultancy sends, opened in a browser inside a session your app issued. Client-side and server-side CSV import names the jobs each of the two models fits.

The pieces you wrote and the next survey

You wrote two tables carrying the same declarations, a schema with eight columns, one synonyms block, a route that streams a chunk over the connection, and one procedure that settles a batch and clears its own staging rows. The file stays on the machine that opened it. The rows travel from your own front end to your own route and into SQL Server. The only party you added to the chain is yourself. Point the same setup at a React CSV importer modal or at the web component and the middle stays the same.

The next survey brings the same consultancy and a fourth spelling of Grade II*. The mappings from this season are already stored, the new spelling costs one more line in synonyms, and a value SQL Server would have rewritten in silence sits in a grid cell instead, in front of the person who measured it.