JSON to SQL INSERT

How the JSON to SQL INSERT works

The JSON is read with a strict parser that reports syntax errors with their line and column and keeps the original digits of every number. The input must be an array of objects (a single object is treated as one row). The columns are every property name found in any object, in the order they first appear; a row without a property gets NULL.

Each value is written as a SQL literal for the chosen dialect: strings in single quotes with quotes doubled (and N'...' for non-ASCII text on SQL Server, escaped backslashes on MySQL), numbers exactly as written, booleans as 1/0 or TRUE/FALSE, and nested objects or arrays as compact JSON text. Rows are grouped into multi-row INSERT ... VALUES statements of the size you choose. With Include CREATE TABLE, a column type is picked from all the values in each column, and a column is NOT NULL only when every row has a value. The statements are only generated as text; nothing is sent to or run on a database.

How to use the JSON to SQL INSERT

1. Paste a JSON array of objects into the input box, open a .json file, or select Load example.
2. Enter the table name (for example dbo.Products), choose the dialect and how many rows go into each INSERT statement.
3. Tick Include CREATE TABLE if you also want a table definition, and untick Quote all names to quote only names that need it.
4. Select Convert to SQL, or press Ctrl + Enter (Cmd + Enter on a Mac), then copy the SQL or download it as a .sql file.

Example

This JSON, with table name dbo.Products, SQL Server and Include CREATE TABLE ticked:

[
  {"id": 1, "name": "Kid's Mouse", "price": 19.5, "inStock": true},
  {"id": 2, "name": "Kopfhörer", "price": 129, "tags": ["audio"]}
]

produces:

CREATE TABLE [dbo].[Products] (
    [id] INT NOT NULL,
    [name] NVARCHAR(50) NOT NULL,
    [price] DECIMAL(4, 1) NOT NULL,
    [inStock] BIT NULL,
    [tags] NVARCHAR(MAX) NULL
);

INSERT INTO [dbo].[Products] ([id], [name], [price], [inStock], [tags])
VALUES
    (1, 'Kid''s Mouse', 19.5, 1, NULL),
    (2, N'Kopfhörer', 129, NULL, '["audio"]');

Common use cases

• Loading test or seed data from a JSON fixture file into a development database.
• Moving records exported from an API or a NoSQL database into a relational table.
• Creating a quick table and INSERT script to explore a JSON dataset with SQL.
• Writing repeatable data setup scripts for integration tests.

Common errors and how to fix them

The JSON must be an array of objects, such as [{"id": 1}]

INSERT statements need rows, so the top level has to be an array of objects. If your API wraps the records in an envelope, paste only the inner array.

The objects have no properties, so there are no columns to insert

The array contains empty objects. Supply at least one record with the fields you want as columns.

An object has an empty property name, which cannot be used as a column name

One record has a key of "". Remove or rename that key, because no database will accept it as a column.

Records have different fields

Columns are taken from the properties found in the data. Make the records consistent before generating, or the statements will not line up with your table definition. Always review generated SQL before running it.

Frequently asked questions