SQL Bulk and Batch Insert by Database: Syntax, Limits & Performance

Inserting multiple rows efficiently is a foundational requirement when loading datasets, processing feeds, or seeding databases. While standard SQL defines the row value constructor VALUES (r1c1, r1c2), (r2c1, r2c2), database engines impose significantly different architectural limits on batch size, parameter counts, and transaction locking.

To quickly generate multi-row INSERT statements from spreadsheets or CSV exports, use our in-browser CSV to SQL converter or experiment with batch statements directly in the SQLite SQL playground.

Syntax and limits at a glance

MySQL and MariaDB

MySQL and MariaDB support multi-row insertion by chaining parenthesized tuples after the VALUES keyword:

INSERT INTO employees (id, name, department, salary)
VALUES 
  (1, 'Alice Smith', 'Engineering', 95000.50),
  (2, 'Bob Jones', 'Marketing', 72000.00),
  (3, 'Charlie Brown', 'Engineering', 105000.00);

Packet size and tuning considerations

• max_allowed_packet: MySQL does not enforce a rigid row-count ceiling on standard multi-row inserts; instead, the entire SQL packet must fit within max_allowed_packet (default 64 MiB in MySQL 8.0/8.4). If a batch exceeds this limit, the server aborts the connection with Error 1153 (ER_NET_PACKET_TOO_LARGE).
• Transaction grouping: When inserting thousands of rows, batching 500 to 1,000 rows per INSERT statement provides optimal throughput, reducing network round-trips and parser overhead without straining buffer memory.
• Bulk loading: For millions of rows, use MySQL’s native LOAD DATA INFILE or MariaDB’s LOAD DATA, which bypasses SQL parsing entirely and loads data directly into storage engine tables. See the MySQL 8.4 INSERT manual and LOAD DATA documentation.

PostgreSQL

PostgreSQL supports ANSI multi-row VALUES constructors and optionally returns generated identity keys using the RETURNING clause:

INSERT INTO employees (id, name, department, salary)
VALUES 
  (1, 'Alice Smith', 'Engineering', 95000.50),
  (2, 'Bob Jones', 'Marketing', 72000.00),
  (3, 'Charlie Brown', 'Engineering', 105000.00)
RETURNING id;

High-throughput bulk loading

• Parameter limits: In client libraries using parameterized statements (extended query protocol), PostgreSQL limits query parameters to 65,535 (int16). If each row contains 10 columns, a single prepared INSERT cannot exceed 6,553 rows.
• UNNEST() array pattern: When sending large batches via parameterized queries, passing parallel arrays to UNNEST() is faster and circumvents parameter count limits:

  INSERT INTO employees (id, name, department, salary)
  SELECT * FROM UNNEST(
    $1::int[], 
    $2::text[], 
    $3::text[], 
    $4::numeric[]
  );
  

• The COPY command: For production ETL and bulk imports exceeding 100,000 rows, COPY employees FROM STDIN WITH (FORMAT csv) is significantly faster than repeated INSERT statements because it writes directly to heap pages. See PostgreSQL’s INSERT documentation and COPY reference.

SQLite

SQLite introduced multi-row VALUES constructors in version 3.7.11 (March 2012):

INSERT INTO employees (id, name, department, salary)
VALUES 
  (1, 'Alice Smith', 'Engineering', 95000.50),
  (2, 'Bob Jones', 'Marketing', 72000.00),
  (3, 'Charlie Brown', 'Engineering', 105000.00);

The transaction sync bottleneck

• Per-statement fsync: By default, SQLite operates in autocommit mode, issuing an expensive disk synchronization (fsync) after every separate SQL statement. Running 10,000 individual INSERT statements without an explicit transaction can take minutes. Wrapping them inside BEGIN TRANSACTION; and COMMIT; reduces disk flushes to a single sync, taking tens of milliseconds.
• Variable limits: Prior to SQLite 3.32.0, the maximum number of host parameters (?) was 999 (SQLITE_LIMIT_VARIABLE_NUMBER). SQLite 3.32.0 increased this default to 32,766. When building dynamic parameterized queries, keep total parameter count below this threshold.
• Test multi-row insertions in our SQLite SQL playground to observe query plans and execution times locally. See SQLite’s INSERT reference and limits manual.

SQL Server (T-SQL)

SQL Server introduced multi-row table value constructors in SQL Server 2008:

INSERT INTO employees (id, name, department, salary)
VALUES 
  (1, N'Alice Smith', N'Engineering', 95000.50),
  (2, N'Bob Jones', N'Marketing', 72000.00),
  (3, N'Charlie Brown', N'Engineering', 105000.00);

The 1,000-row constructor limit

• Error 10738: SQL Server enforces a strict maximum of 1,000 row value constructors in a single INSERT ... VALUES statement:

  “Msg 10738, Level 15, State 1: The number of row value expressions in the INSERT statement exceeds the maximum allowable number of 1000 row values.” Any application loading bulk data via multi-row VALUES must partition its records into batches of 1,000 or fewer.

• The 2,100 parameter limit: SQL Server limits total query parameters to 2,100. If each row has 10 columns, a parameterized batch can hold at most 210 rows.
• Table-Valued Parameters (TVPs) and BULK INSERT: For high-volume batches from C# / .NET, use Table-Valued Parameters or SqlBulkCopy. In T-SQL scripts, use BULK INSERT employees FROM 'C:\data.csv' WITH (FORMAT = 'CSV');. See Microsoft’s Table Value Constructor documentation and BULK INSERT manual.

Oracle Database

Oracle’s syntax for multi-row insertion depends on your database release:

Oracle Database 23c / 26

Starting in Oracle Database 23c, Oracle natively supports the standard multi-row VALUES syntax:

-- Supported in Oracle Database 23c and later
INSERT INTO employees (id, name, department, salary)
VALUES 
  (1, 'Alice Smith', 'Engineering', 95000.50),
  (2, 'Bob Jones', 'Marketing', 72000.00),
  (3, 'Charlie Brown', 'Engineering', 105000.00);

Oracle Database 19c, 12c, and 11g

Older versions of Oracle reject comma-separated VALUES (), () with ORA-00933: SQL command not properly ended. For releases prior to 23c, developers use unconditional INSERT ALL:

-- Legacy syntax for Oracle 19c and earlier
INSERT ALL
  INTO employees (id, name, department, salary) VALUES (1, 'Alice Smith', 'Engineering', 95000.50)
  INTO employees (id, name, department, salary) VALUES (2, 'Bob Jones', 'Marketing', 72000.00)
  INTO employees (id, name, department, salary) VALUES (3, 'Charlie Brown', 'Engineering', 105000.00)
SELECT 1 FROM DUAL;

Another common alternative uses UNION ALL:

INSERT INTO employees (id, name, department, salary)
SELECT 1, 'Alice Smith', 'Engineering', 95000.50 FROM DUAL
UNION ALL
SELECT 2, 'Bob Jones', 'Marketing', 72000.00 FROM DUAL
UNION ALL
SELECT 3, 'Charlie Brown', 'Engineering', 105000.00 FROM DUAL;

For massive data sets, use SQL*Loader or create an External Table referencing a remote data file. See Oracle’s 23c SQL language reference and SQL*Loader guide.

Best practices summary

1. Chunk records into batches: For multi-row INSERT statements, chunk rows into batches of 500 to 1,000. This avoids memory spikes and respects SQL Server’s 1,000-row limit.
2. Always wrap inserts in transactions: In SQLite, PostgreSQL, and MySQL, wrap repeated inserts in BEGIN TRANSACTION; and COMMIT; to prevent per-statement disk sync bottlenecks.
3. Use the browser converter: When converting CSV or TSV files into SQL statements, try the online CSV to SQL converter to automatically generate schema-valid CREATE TABLE and chunked INSERT statements tailored to your database dialect.
4. Format queries: When troubleshooting large batch SQL scripts, use the SQL formatter to verify clause boundaries and parentheses.