How to Generate SQL INSERTs from JSON or CSV (Safely)

Turning JSON or CSV into SQL INSERT statements is a mechanical mapping — object/row → row, key/header → column — but two details decide whether the result runs or blows up: typing each value correctly, and escaping single quotes in strings. Get those right and you can bulk-load a JSON dump or a spreadsheet in seconds. The JSON to SQL and CSV to SQL converters handle both in your browser, and the SQL IN Clause Generator covers the related “paste a list into a WHERE” case.

The mapping

Both formats describe rows of data; only the column source differs:

	Columns come from	Each row is
JSON	the union of the objects’ keys	one object in the array
CSV	the header (first) row	each subsequent line

So [{"id":1,"name":"Ada"}] and id,name + 1,Ada both become:

INSERT INTO my_table (id, name) VALUES
(1, 'Ada');

Where it actually breaks: typing and escaping

The mapping is the easy part. These two rules are what a naive converter gets wrong.

1. Type each value. SQL literals aren’t all quoted:

• Numbers → bare: 42, 3.5
• Booleans → TRUE / FALSE
• Empty / null → NULL
• Everything else → a single-quoted string

2. Escape single quotes by doubling them. This is the one that bites everyone:

-- WRONG — the apostrophe ends the string early, breaking the statement:
INSERT INTO t (name) VALUES ('O'Brien');

-- RIGHT — double the quote inside the literal:
INSERT INTO t (name) VALUES ('O''Brien');

That doubling ('' = one literal ') is standard ANSI SQL and works in PostgreSQL, MySQL and SQLite. It’s also the difference between a script that imports cleanly and one that fails halfway — or, with untrusted input, a SQL injection.

Here is the full typing decision, from source value to the literal that lands in the statement:

Source value	SQL literal	Note
42, 3.5, -7	42, 3.5, -7	Numeric — no quotes
true / false (JSON boolean)	TRUE / FALSE	Postgres native; MySQL maps to 1/0
null (JSON) or empty CSV cell	NULL	Not the string 'NULL' and not ''
"Ada"	'Ada'	Ordinary string, single-quoted
"O'Brien"	'O''Brien'	Embedded quote doubled
"line1\nline2"	'line1\nline2'	Newline kept inside the literal

Two subtleties worth calling out. First, NULL and the empty string '' are not the same in SQL: a genuinely missing value should become NULL, while a deliberately blank text field stays ''. If your source can’t distinguish them, decide the rule up front. Second, boolean handling is dialect dependent — PostgreSQL accepts TRUE/FALSE literally, whereas older MySQL treats them as aliases for 1/0, so the safest portable output for a boolean column is often just 1 or 0.

→

INSERT INTO t (id, name, active) VALUES (1, 'Ada', TRUE), (2, 'O''Brien', FALSE); numbers bare · bool TRUE/FALSE · '' escapes the quote

CSV has an extra trap: parsing

A spreadsheet value like "Bo, Jr" contains a comma inside a field. Splitting on every comma shifts all your columns and corrupts the import. A correct converter uses an RFC 4180 parser that respects quoted fields, escaped quotes (""), and even newlines inside quotes — so "Bo, Jr" stays one value. That’s why CSV to SQL parses properly rather than split(','), and why it also lets you pick a delimiter for semicolon (European) or tab-separated data.

A worked example, end to end

Take this small JSON export, which deliberately hits every tricky case — a number, a boolean, an apostrophe, a genuine null, and a blank string:

[
  {"id": 1, "name": "Ada",     "active": true,  "notes": "founder"},
  {"id": 2, "name": "O'Brien", "active": false, "notes": null},
  {"id": 3, "name": "Bo, Jr",  "active": true,  "notes": ""}
]

Applying the mapping and the two rules produces:

INSERT INTO users (id, name, active, notes) VALUES
(1, 'Ada', TRUE, 'founder'),
(2, 'O''Brien', FALSE, NULL),
(3, 'Bo, Jr', TRUE, '');

Notice what each row demonstrates: row 1 quotes strings but leaves the number and boolean bare; row 2 doubles the apostrophe in O''Brien and turns JSON null into the keyword NULL (unquoted); row 3 keeps the comma inside 'Bo, Jr' as data and preserves the empty string as '' rather than collapsing it to NULL. Feed the identical data in CSV form (id,name,active,notes as the header) and you get the same statement — only the column source changes.

One row per statement, or one big INSERT?

Two valid shapes, and it’s a real choice:

• Multi-row (VALUES (…),(…),(…);) — fewer statements, faster bulk loads. The default.
• One INSERT per row — easier to diff in version control, comment out, or run selectively.

For very large loads there is a practical ceiling: a single multi-row statement can bump into engine limits — MySQL’s max_allowed_packet, for instance — so tens of thousands of rows are usually split into batches of a few hundred to a few thousand rows each. That keeps every statement well under the limit while still avoiding the per-statement overhead of one INSERT per row. If you are loading millions of rows, a purpose-built path such as PostgreSQL’s COPY or MySQL’s LOAD DATA will beat any generated INSERT script; the converters here are aimed at the small-to-medium seed, migration, and fixture files that make up the vast majority of day-to-day jobs.

The related case: a SQL IN clause

Sometimes you don’t need INSERTs — you have a list and need WHERE col IN (...). Same escaping rules apply: numbers stay bare, strings get quoted. Pasting a column of IDs from a spreadsheet and hand-adding quotes and commas is exactly the tedious, error-prone job the SQL IN Clause Generator removes — it auto-detects numbers vs strings and builds the parenthesised list for you.

A word on safety

Escaping quotes makes these generated scripts safe to run on data you control — migrations, seed data, one-off imports. It is not a substitute for parameterised queries in application code: when you’re handling untrusted user input at runtime, use prepared statements, not string-built SQL. These converters are build-time tools, and — because they run entirely in your browser — the database dumps and exports you paste never leave your device.

The bottom line

Generating SQL from JSON or CSV is a mapping plus two rules: type every value (numbers and booleans bare, empty as NULL, the rest quoted) and escape single quotes by doubling them. Parse CSV properly so commas inside fields don’t shift columns, pick multi-row or per-row to taste, and reach for an IN clause generator when you just need a list — all locally, with the JSON to SQL, CSV to SQL and IN clause tools.