How to Import CSV Into Azure SQL Database

Azure SQL Database documents several ways to move a CSV into a table. The bcp utility loads a file from a command line. BULK INSERT reads one out of Azure Storage. The Import Flat File Wizard in SQL Server Management Studio copies a file "to a new table in your database". Azure Data Factory schedules the whole thing. Every one of those starts from a seat that already holds the database. Your customer sits outside that seat, holding the fuel card statement their card platform emailed this morning.

The ceiling a request hits

The obvious design writes one parameter per cell. Microsoft closes that road in a single sentence. "You're limited to a total of 2,100 query parameters, so this limits the total number of rows that can be processed in this manner." A ten-column row therefore fits 210 times into one statement, and the 211th raises error 8003, "The incoming request has too many parameters. The server supports a maximum of %d parameters."

Microsoft names its own way past that. A table-valued parameter is one parameter carrying many rows, "declared by using user-defined table types", and it sends them "without creating a temporary table or many parameters". Inside the database "Transact-SQL passes table-valued parameters to routines by reference to avoid making a copy of the input data". The rows are "strongly typed", and they "do not acquire locks for the initial population of data from a client". The recommendation is stated plainly. "If you choose a single batching technique, table-valued parameters offer the best performance and flexibility."

One more sentence decides the rest of this article. "Other techniques, such as SQL bulk copy, only permit the insertion of new rows. But with table-valued parameters, you can use logic in the stored procedure to determine which rows are updates and which are inserts."

The files that arrive

Two card issuers send September in the same week. Each one exports from a different card platform.

1Card No,VRM,Driver,Txn Date,Site,Product,Litres,Net,Odo,Ref27083440012345678,AK21 XVR,J. Okonkwo,03/09/2026,BP Keele M6,DSL,412.550,566.21,184220,ALS-9912037083440012345678,AK21 XVR,J. Okonkwo,03/09/2026,BP Keele M6,DSL,412.550,566.21,184220,ALS-9912047083440012345678,AK21 XVR,J. Okonkwo,17/09/2026,Moto Donington,SHOP,0.000,9.40,,ALS-9918857083440012399001,LT70 HGB,A. Whitfield,21/09/2026,Shell Lymm,ADB,18.000,31.05,197004,ALS-99241⋮297 rows not shown3037083440012399001,LT70 HGB,A. Whitfield,30/09/2026,BP Keele M6,DSL,395.220,542.85,201338,ALS-99904

1Card Number,VRN,Cardholder,Transaction Date,Location,Fuel Grade,Litres Dispensed,Value Net,Mileage,Transaction Reference26503440077881234,DV19 KLM,R. Petrescu,2026-09-04,Esso Rugby,ULSD,388.140,531.75,301880,UKF-447136503440077881234,DV19 KLM,R. Petrescu,2026-09-18,Applegreen Corley,Unleaded,52.900,74.06,304115,UKF-4530⋮211 rows not shown2156503440077885000,,R. Petrescu,2026-09-22,Esso Rugby,Truck Wash,,26.00,,UKF-4972

The first issuer calls the registration VRM and the second calls it VRN. One writes dates day first and the other writes them ISO. DSL, ULSD and ADB are the trade's shorthand for two fuel grades. The first file repeats ALS-99120 on two lines, because the platform re-sent it. Row 4 of that file is a shop purchase carrying 0.000 litres. The last row of the second file has no registration at all.

The person drags both files into the importer inside your app. Updog Importer reads them in the browser, matches each file's headers to your schema, holds the products to your list, and puts every row in front of them. Your onComplete handler receives the rows grouped by file. The handler posts them to a route you own, and that route hands each chunk to a stored procedure.

No Updog server stands between the browser and Azure SQL.

The table and the type in front of it

One table holds the transactions, keyed by the issuer and the issuer's own reference.

CREATE TABLE dbo.card_transaction
(
    issuer           NVARCHAR(20)  NOT NULL,
    transaction_ref  NVARCHAR(30)  NOT NULL,
    card_number      NVARCHAR(19)  NOT NULL,
    vehicle_reg      NVARCHAR(12)  NOT NULL,
    driver_name      NVARCHAR(80)  NOT NULL,
    transaction_date DATE          NOT NULL,
    site_name        NVARCHAR(60)  NOT NULL,
    product_type     NVARCHAR(20)  NOT NULL,
    litres           DECIMAL(9, 3) NULL,
    net_amount       DECIMAL(11,2) NOT NULL,
    odometer         INT           NULL,
    updated_at       DATETIME2(0)  NOT NULL,
    CONSTRAINT pk_card_transaction
        PRIMARY KEY (issuer, transaction_ref),
    CONSTRAINT ck_card_transaction_litres CHECK
    (
        (product_type IN ('Diesel', 'Petrol', 'AdBlue') AND litres > 0)
        OR
        (product_type NOT IN ('Diesel', 'Petrol', 'AdBlue') AND litres IS NULL)
    )
);

The CHECK constraint reads two columns at once. A diesel, petrol or AdBlue line carries litres above zero, and a shop, wash or toll line carries none. That is the rule the third line of the first file breaks, and a row breaking it raises error 547, "The %ls statement conflicted with the %ls constraint".

nvarchar(20) counts byte-pairs, since "n never defines numbers of characters that can be stored", and a character above the basic plane can take two of them. A driver name in Latin script never reaches that boundary.

The type in front of the table declares the shape of one chunk.

CREATE TYPE dbo.card_transaction_batch AS TABLE
(
    op               NVARCHAR(6)   NOT NULL,
    issuer           NVARCHAR(20)  NOT NULL,
    transaction_ref  NVARCHAR(30)  NOT NULL,
    card_number      NVARCHAR(19)  NULL,
    vehicle_reg      NVARCHAR(12)  NULL,
    driver_name      NVARCHAR(80)  NULL,
    transaction_date DATE          NULL,
    site_name        NVARCHAR(60)  NULL,
    product_type     NVARCHAR(20)  NULL,
    litres           DECIMAL(9, 3) NULL,
    net_amount       DECIMAL(11,2) NULL,
    odometer         INT           NULL
);

The op column is Microsoft's own suggestion. "The table type can also be modified to contain an "Operation" column that indicates whether the specified row should be inserted, updated, or deleted." Every column past the key is nullable here, so a delete row travels carrying its key alone.

The schema in Updog Importer

The columns array describes the table as the person sees it.

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

const PRODUCTS = ["Diesel", "Petrol", "AdBlue", "Shop", "Wash", "Toll"];
const FUELS = ["Diesel", "Petrol", "AdBlue"];

export const columns: DataEditorColumn[] = [
  {
    id: "transactionRef",
    title: "Transaction ref",
    size: 160,
    transformer: (value) => String(value).trim().toUpperCase(),
    validators: [{ type: "required" }, { type: "unique" }],
  },
  {
    id: "cardNumber",
    title: "Card number",
    size: 190,
    validators: [
      { type: "required" },
      { type: "regex", pattern: "^[0-9]{16,19}$" },
    ],
  },
  {
    id: "vehicleReg",
    title: "Vehicle reg",
    size: 140,
    transformer: (value) => String(value).trim().toUpperCase(),
    validators: [{ type: "required" }],
  },
  {
    id: "driverName",
    title: "Driver name",
    size: 170,
    validators: [{ type: "required" }],
  },
  {
    id: "transactionDate",
    title: "Transaction date",
    size: 160,
    editor: { type: "date" },
    validators: [{ type: "required" }, { type: "date" }],
  },
  {
    id: "siteName",
    title: "Site name",
    size: 180,
    validators: [{ type: "required" }],
  },
  {
    id: "productType",
    title: "Product type",
    size: 150,
    editor: { type: "select", options: PRODUCTS, enableCustomValue: false },
    validators: [{ type: "required" }, { type: "oneOf", values: PRODUCTS }],
    dependentFields: ["litres"],
  },
  {
    id: "litres",
    title: "Litres",
    size: 130,
    editor: { type: "number" },
    validators: [
      { type: "function", fn: litresAgainstProduct },
      { type: "number", min: 0, max: 5_000, decimalPlaces: 3 },
    ],
  },
  {
    id: "netAmount",
    title: "Net amount",
    size: 140,
    editor: { type: "number" },
    validators: [{ type: "required" }, { type: "number", decimalPlaces: 2 }],
  },
  {
    id: "odometer",
    title: "Odometer",
    size: 130,
    editor: { type: "number" },
    validators: [{ type: "number", min: 0, max: 2_000_000, decimalPlaces: 0 }],
  },
];

Each editor earns its place against what arrives. The date editor turns 17/09/2026 into 2026-09-17, and since 17 is above 12 the first file settles day first, so 03/09/2026 lands as 2026-09-03. That verdict matters on the way out, because SQL Server reads a date string by the session's language setting, and yyyy-MM-dd is "the only format defined as an international standard". The select editor holds the product to six values, and a value nobody maps is dropped from the row. { type: "required" } on the registration catches the blank cell while somebody can still type into it.

The rule that reads two columns

No single-column validator can see the table's CHECK, since the answer for litres depends on the product type sitting beside it.

function litresAgainstProduct(value, row) {
  const isFuel = FUELS.includes(String(row.productType));
  const litres = Number(value);
  const filled = value !== null && value !== "" && litres > 0;

  if (isFuel && !filled) {
    return { level: "error", message: "A fuel line needs litres above zero" };
  }
  if (!isFuel && value !== null && value !== "") {
    return { level: "error", message: "Only a fuel line carries litres" };
  }
  return null;
}

{ type: "function" } receives the cell value and the whole row, so the rule reads row.productType and decides. Every cell is validated as the rows land, so the 0.000 on the shop line is flagged the moment the grid appears. dependentFields points forward, from the column being edited to the columns that need rechecking, so the entry sits on productType and names litres. Change a line from Shop to Diesel and the litres cell is judged again against its new neighbour.

Two rules cover the same ground twice on purpose. The browser refuses the row a person can still fix, and the constraint refuses anything that reaches the table another way.

The headers each issuer sends

Column matching repeats per file. Each file gets its own screen and its own mapping, so the same schema column is fed by a different header in each one.

Site is four characters, exactly at the contains floor, which fires once the shorter string runs to four. Net is three and reaches nothing that way, though it is one of the two words in netAmount, so word overlap carries it. Location needs no configuration, since the built-in synonym table already groups it with sitename. VRM, VRN, Odo and Mileage are all too short or too far for any tier, so four synonyms entries cover six headers across the two files.

Value matching runs once for the whole import and collects values per schema column across both files. SHOP equals its option and Truck Wash contains Wash, which lands at eighty. DSL, ULSD, ADB and Unleaded reach nothing, so three more synonyms entries carry the trade's shorthand.

Each file also settles its own dates. The verdict comes from a sample of that file alone, so the day-first export and the ISO export never borrow each other's reading.

The mount

The props tie the two files, the schema and the table together.

<DataEditor<CardTransaction>
  apiKey="your-license-key"
  open={open}
  onClose={closeEditor}
  columns={columns}
  primaryKey="transactionRef"
  enableDeleteRow="all"
  blockSubmitOnError
  synonyms={{
    columns: {
      vehicleReg: ["vrm", "vrn"],
      driverName: ["cardholder"],
      productType: ["fuel grade"],
      odometer: ["odo", "mileage"],
    },
    values: {
      Diesel: ["dsl", "ulsd"],
      AdBlue: ["adb"],
      Petrol: ["unleaded"],
    },
  }}
  onComplete={onComplete}
/>

primaryKey names the reference column. Every column it names has to be a column the schema declares, and the issuer is not one of them, since it comes from the file the rows arrived in rather than from any cell. The table keeps its two-column key for the day two issuers hand out the same reference, and the browser holds every reference in the session apart from every other. Values are compared after trimming, and a row with no reference matches nothing and arrives as new.

{ type: "unique" } on that same column flags the repeated ALS-99120 line as both copies land, and enableDeleteRow="all" lets the person drop one by hand. blockSubmitOnError keeps submit disabled while any row carries an error, the duplicate reference, the blank registration and the shop line's litres included. Whatever the person fixes by hand comes back on the result as learnedSynonyms, ready to store and feed back through synonyms next month.

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 file lands as its own entry, carrying the file name, so the row that came from UK Fuels still says so on the way out.

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

const CHUNK_SIZE = 1_000;

const toRow = (entry: ResultRow<CardTransaction>, issuer: string) => {
  if (entry.isDeleted && entry.isNew) return [];
  return [{
    op: entry.isDeleted ? "delete" : "upsert",
    issuer,
    transaction_ref: entry.row.transactionRef,
    card_number: entry.row.cardNumber,
    vehicle_reg: entry.row.vehicleReg,
    driver_name: entry.row.driverName,
    transaction_date: entry.row.transactionDate,
    site_name: entry.row.siteName,
    product_type: entry.row.productType,
    litres: entry.row.litres === "" ? null : Number(entry.row.litres),
    net_amount: Number(entry.row.netAmount),
    odometer: entry.row.odometer === "" ? null : Number(entry.row.odometer),
  }];
};

const onComplete = useCallback(async (result: DataEditorResult<CardTransaction>) => {
  for (const source of result.sources) {
    const issuer = issuerFor(source.sourceName);
    const rows = source.rows.flatMap((entry) => toRow(entry, issuer));

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

Three flags become one field. A new transaction and a changed one are the same payload, since the MERGE decides which by looking at the table. A row the person added and then deleted goes nowhere. Nothing here loads from your backend, so every row arrives new and op reads upsert on all of them. The delete branch waits on the month you feed the stored transactions in through loadData.

The chunk size sits inside Microsoft's own ladder. It publishes three rungs, a single parameterized INSERT under 100 rows, table-valued parameters under 1,000, and SqlBulkCopy at 1,000 and above. Bulk copy is refused here because it inserts only, so 1,000 is the top of the rung this design lives on. Microsoft's batch-size test points the same way, since 1,000 rows in one batch beat ten batches of 100 and twenty of 50, and it notes "there was typically no advantage to breaking large batches into smaller chunks".

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 both statements clear 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 writes

The route holds the credentials, and the browser stops there.

import sql from "mssql";

const pool = new sql.ConnectionPool({
  server: process.env.SQL_SERVER,
  database: process.env.SQL_DATABASE,
  authentication: { type: "azure-active-directory-msi-app-service" },
  options: { encrypt: true },
});

const COLUMNS = [
  ["op", sql.NVarChar(6), false],
  ["issuer", sql.NVarChar(20), false],
  ["transaction_ref", sql.NVarChar(30), false],
  ["card_number", sql.NVarChar(19), true],
  ["vehicle_reg", sql.NVarChar(12), true],
  ["driver_name", sql.NVarChar(80), true],
  ["transaction_date", sql.Date, true],
  ["site_name", sql.NVarChar(60), true],
  ["product_type", sql.NVarChar(20), true],
  ["litres", sql.Decimal(9, 3), true],
  ["net_amount", sql.Decimal(11, 2), true],
  ["odometer", sql.Int, true],
];

const dedupe = (rows) => {
  const latest = new Map();
  for (const row of rows) latest.set(row.issuer + "|" + row.transaction_ref, row);
  return [...latest.values()];
};

const toTable = (rows) => {
  const table = new sql.Table("dbo.card_transaction_batch");
  for (const [name, type, nullable] of COLUMNS) {
    table.columns.add(name, type, { nullable });
  }
  for (const row of rows) {
    table.rows.add(...COLUMNS.map(([name]) => row[name] ?? null));
  }
  return table;
};

azure-active-directory-msi-app-service means the route holds no password at all. The host hands it a token for its managed identity, and the database grants that identity the rights it needs.

getVerifiedSession() stands in for your own server-side authentication check. The issuer arrives in the body, so the route checks it against the issuers that session is allowed to load. A guessed name reaches no other fleet's transactions.

dedupe is there because the MERGE refuses a source that matches one target row twice. Microsoft says so directly. "The MERGE statement can't update the same row more than once, or update and delete the same row." The browser already flagged the repeated ALS-99120 line and the person deleted one, and the route runs the map anyway, because a route your app can call is a route anyone with a session can call. The map keeps the last of any pair that reaches it.

sql.Table builds the parameter with the same column names and types the database type declares, in the same order, because the driver matches them by position.

The procedure that merges

One call, one statement, one transaction.

CREATE PROCEDURE dbo.merge_card_transactions
    @batch dbo.card_transaction_batch READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION;

    MERGE dbo.card_transaction WITH (HOLDLOCK) AS target
    USING @batch AS source
        ON  target.issuer = source.issuer
        AND target.transaction_ref = source.transaction_ref

    WHEN MATCHED AND source.op = 'delete' THEN
        DELETE

    WHEN MATCHED THEN UPDATE SET
        card_number      = source.card_number,
        vehicle_reg      = source.vehicle_reg,
        driver_name      = source.driver_name,
        transaction_date = source.transaction_date,
        site_name        = source.site_name,
        product_type     = source.product_type,
        litres           = source.litres,
        net_amount       = source.net_amount,
        odometer         = source.odometer,
        updated_at       = SYSUTCDATETIME()

    WHEN NOT MATCHED BY TARGET AND source.op <> 'delete' THEN
        INSERT (issuer, transaction_ref, card_number, vehicle_reg, driver_name,
                transaction_date, site_name, product_type, litres, net_amount,
                odometer, updated_at)
        VALUES (source.issuer, source.transaction_ref, source.card_number,
                source.vehicle_reg, source.driver_name, source.transaction_date,
                source.site_name, source.product_type, source.litres,
                source.net_amount, source.odometer, SYSUTCDATETIME());

    COMMIT TRANSACTION;
END;

READONLY is required on every table parameter, since you "cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine". So the parameter is the source and never the target.

Three branches read the op column. A matched row marked delete is deleted, any other matched row is updated, and an unmatched row that is not a delete is inserted. HOLDLOCK is on the target because Microsoft recommends it here, since "in some scenarios where unique keys are expected to be both inserted and updated by the MERGE, specifying the HOLDLOCK will prevent against unique key violations".

Nothing is staged. The parameter is the source table, so there is no temporary table to create, fill and drop, and the whole chunk commits or rolls back together.

The errors that come back by number

mssql raises a RequestError whose err.number is the database's own error number, so the route can turn each one into a sentence.

const MESSAGES = {
  2627: "One reference is already stored under this issuer",
  2601: "One reference is already stored under this issuer",
  2628: "A value is longer than its column allows",
  547:  "A row breaks a rule on the table, most likely litres against product",
  8672: "The same reference arrived twice in one batch",
  8003: "The batch carried too many parameters",
};

const explain = (error) => MESSAGES[error.number] ?? "The write did not finish";

Each number carries a published message. 2628 reads "String or binary data would be truncated in table '%.*ls', column '%.*ls'. Truncated value: '%.*ls'", so a row that overflows a width names the column and the value on its way back. 8672 is the MERGE matching one target row twice. Importing a CSV into PostgreSQL shows the other relational destination in this cluster answering a failed write with its own five-character codes.

A second group is worth sending again. Azure SQL publishes a list of transient fault codes, among them 40501, "The service is currently busy", and 40613, "Database '%.*ls' on server '%.*ls' is not currently available". The resource errors 10928 and 10929 sit under resource governance instead, and both appear on the six-number transient list tedious carries from Microsoft's own SqlClient. A number that lands while the request is in flight arrives as a RequestError and the loop below catches it. The same number during login arrives as a ConnectionError with the code ELOGIN and no number on it, and tedious retries that itself, three times by default. Microsoft asks for a specific cadence. "We recommend that you delay for 5 seconds before your first retry… For each subsequent retry, the delay should grow exponentially, up to a maximum of 60 seconds."

That cadence sizes the retry loop, because all of it happens with the confirm dialog open and a spinner on the button. Five, ten and twenty seconds is thirty-five seconds on top of the write, which is about as long as a person will sit before the thrown error puts them back on their own rows. 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 Azure SQL connector, no destination list, no webhook and no server of ours. onComplete hands your code an object, and the route and the stored procedure in the middle are work you do. Our uniqueness rule reads one column, so a key spanning two is checked by your own code.

Azure SQL already ships its own ways in for the other case. bcp reads a UTF-8 file once you name the code page, -C 65001, and that switch is Windows only, "not supported on Linux and macOS". BULK INSERT reads from Azure Storage, needs ADMINISTER DATABASE BULK OPERATIONS, and "doesn't support skipping headers". The Import Flat File Wizard writes to a new table whose name "should be unique", infers types "based on the first 200 rows", and uses "encoding based on the system's active code page. On most machines this defaults to ANSI." For a one-off load your own team assembled, those are the shorter ways in. Everything above exists for the statements your fleet's card issuers send, 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 next month

You wrote one table with a two-column key and a two-column CHECK, a table type carrying an operation column, a schema with ten columns, one validator that reads two of them, four synonyms entries for the headers and three for the values, a dedupe map, one route that sends 1,000 rows as a single parameter, and one procedure that merges them inside a transaction. The files stay on the machine that opened them. The rows travel from your own front end to your own route and into Azure SQL, and 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.

Next month brings the same two issuers and a third one with a third spelling of Fuel grade. The mappings from September are already stored, the table type is unchanged, and the new issuer costs one more line in synonyms.