Skip to content
Json Works

How to Generate SQL INSERT Statements from JSON

Updated

You have a JSON export, an API dump or a fixture file, and you need it in a database. Writing the CREATE TABLE and a few hundred INSERT statements by hand is dull and invites mistakes. Generating them from the data takes a minute.

The quick way

  1. Paste a JSON array of objects into JSON to SQL.
  2. Choose your database: PostgreSQL, MySQL, SQLite, SQL Server or Oracle.
  3. Set the table name.
  4. Copy or download the CREATE TABLE and INSERT statements.

For this input:

[
  { "id": 1, "name": "Ada", "active": true, "score": 9.5 },
  { "id": 2, "name": "Grace", "active": false, "score": null }
]

the PostgreSQL output looks like:

CREATE TABLE users (
  id INTEGER,
  name TEXT,
  active BOOLEAN,
  score NUMERIC
);

INSERT INTO users (id, name, active, score) VALUES
  (1, 'Ada', TRUE, 9.5),
  (2, 'Grace', FALSE, NULL);

How types are chosen

The generator looks at the values in each column across all records and picks the narrowest type that fits every one. Dialects differ:

JSON value PostgreSQL MySQL SQLite SQL Server Oracle
integer INTEGER / BIGINT INT / BIGINT INTEGER INT / BIGINT NUMBER
decimal NUMERIC DECIMAL / DOUBLE REAL FLOAT NUMBER
string TEXT VARCHAR / TEXT TEXT NVARCHAR VARCHAR2
boolean BOOLEAN BOOLEAN (TINYINT(1)) INTEGER BIT NUMBER(1)
date string TIMESTAMP DATETIME TEXT DATETIME2 TIMESTAMP
object / array JSONB JSON TEXT NVARCHAR(MAX) CLOB

Treat the result as a first draft. A generator sees only your sample, so it cannot know that name should be VARCHAR(100) or that id is the primary key.

Things to fix by hand

Add a primary key. If there is a unique id, change the column to id INTEGER PRIMARY KEY. Without one, add a surrogate key, such as id SERIAL PRIMARY KEY in PostgreSQL.

Add NOT NULL and constraints. If a column is never null in your data and should never be, say so. Add UNIQUE, CHECK and foreign keys where they apply.

Tighten string sizes. TEXT is fine in PostgreSQL and SQLite. MySQL and SQL Server index better on bounded types, so set realistic VARCHAR(n) lengths.

Check numerics. Money should be NUMERIC(12,2) or DECIMAL(12,2), never a float. A column of whole-number-looking values that may later hold decimals should be NUMERIC.

Large IDs. Integers beyond 2^53 may have been rounded before they reached the JSON, but if they arrived intact, use BIGINT or NUMERIC(20,0).

Dates. JSON has no date type, so ISO strings like 2026-10-17T09:30:00Z are strings until the database converts them. Use TIMESTAMPTZ in PostgreSQL if the data carries time zones.

Nested data

SQL tables are flat, so nested objects and arrays need a decision:

  1. Flatten the objects into columns like address_city. See how to flatten nested JSON.
  2. Store the nested value as JSON in a JSONB or JSON column, which is good when you rarely query inside it. PostgreSQL and MySQL both support querying JSON columns.
  3. Split arrays into child tables, with a foreign key back to the parent. This is the relational answer and works best when you need to join or aggregate. The trade-offs are the same as in handling arrays when converting to a table.

Quoting and escaping

Generated values are escaped for you: a single quote in a string, as in O'Brien, becomes 'O''Brien'. Table and column names that are reserved words, such as order, user or group, are quoted with the dialect’s identifier quotes ("..." in PostgreSQL and SQLite, backticks in MySQL, [...] in SQL Server). It is still safer to avoid reserved words as names.

Never build SQL by concatenating untrusted input in application code. Generated INSERT scripts are for loading data you already hold, such as fixtures and one-off imports. Use parameterised queries in your applications.

Loading the script

  • PostgreSQL: psql -d mydb -f insert.sql
  • MySQL: mysql mydb < insert.sql
  • SQLite: sqlite3 my.db < insert.sql
  • SQL Server: run in SSMS or with sqlcmd -i insert.sql

Wrap large loads in a transaction so a failure does not leave half the data behind:

BEGIN;
-- inserts here
COMMIT;

For very large datasets, INSERT statements are slow. A CSV with COPY (PostgreSQL), LOAD DATA INFILE (MySQL) or .import (SQLite) is usually much faster. You can get a CSV with JSON to Table, as described in how to convert Postman JSON to CSV.

Checking the load

After loading:

SELECT count(*) FROM users;
SELECT * FROM users LIMIT 5;

Compare the count with the number of records in your JSON array, and spot-check the first and last rows against the source.

Troubleshooting

“Invalid JSON”. Fix syntax first; see common JSON errors.

Columns missing. Columns come from the keys across all records. If a rare key is absent from your sample, the column won’t exist.

Wrong type chosen. One stray string in a numeric column makes the whole column text. Clean the data or fix the type by hand.

Generated script is huge. Split the input, or load via CSV instead.

To generate matching application types, see JSON to TypeScript. To inspect your data before loading, view it as a table.

More guides

All guides · FAQ