Format Code Now
/
ConvertJSON to SQL INSERT Next: TSV to CSV ›

JSON to SQL INSERT Statements

CV-12

Paste a JSON array of objects and get INSERT statements, one row per object. Columns are the keys, names are quoted for your dialect, and big numbers stay exact.

Runs in your browser. Nothing is uploaded.

JSON array of objects (optional first line: -- table: dbo.Customers)

SQL

How to use it

  1. Paste your JSON array into the input box, or open a .json file (up to 5 MB) or drop it on the box.
  2. Choose the Dialect and a Table name. For your own name, put -- table: dbo.Customers as the first line of the input.
  3. Set Rows per INSERT, and whether an empty string becomes NULL.
  4. Copy the SQL or download it as a .sql file, read it over, then run it in your database tool.

How objects become rows

The input should be an array of flat objects, one object per row. A single object gives one row. The columns are all the keys that appear, in the order they are first seen, and a key that one object lacks becomes NULL in that row. Strings are single-quoted with quotes doubled. Numbers are written exactly as typed, so 12345678901234567890 is not rounded. null becomes NULL.

Booleans, nested values and dialects

true and false become TRUE and FALSE in PostgreSQL and MySQL, and 1 and 0 in T-SQL and SQLite, because T-SQL has no boolean literals and SQLite only added them in version 3.23. A nested object or array is stored as JSON text in a quoted string, so it fits a text or JSON column. Names are quoted with [brackets] for T-SQL, double quotes for PostgreSQL and SQLite, and backticks for MySQL. T-SQL text with non-ASCII characters is written as N'...'.

Limits to know about

This writes data, not a schema. It does not create the table or pick column types, so make sure the table exists and the columns match. SQL Server accepts at most 1,000 rows in one INSERT ... VALUES, so T-SQL output is split at 1,000 even if you choose All rows. MySQL backslash doubling assumes the default mode. Look through the generated SQL before running it on a live database.

Error messages and what to do

MessageWhat it meansFix
Item 2 is not an object. Expected an array of objects, one object per row.The array holds a value that is not an object, such as a number, string or another array.Make every item an object, or use only the array items that are rows.
The array is empty, so there are no rows to insert.The JSON is [] and has nothing to turn into INSERT statements.Add at least one object.
Input is not valid JSON: Expected a string key at line 1, column 9The JSON has a syntax error. Here, a trailing comma sits before the closing brace.Fix the JSON at the reported line and column, then convert again.

Questions people ask

How do I convert JSON to SQL INSERT statements?

Paste a JSON array of objects into the input box, pick the Dialect and a table name, and the statements appear. Copy them or download a .sql file. Check them before you run them. It all runs in your browser.

What JSON shape does it expect?

An array of flat objects, one object per row. A single object gives one row. Items that are not objects are reported with their position. Nested values are allowed and are stored as JSON text in a quoted string.

What happens when objects have different keys?

The columns are the union of every key, in the order first seen. If an object lacks a key, that row gets NULL for the column. The statement always lists every column, so each row has the same number of values.

How are true, false and null written?

null is NULL. true and false are TRUE and FALSE for PostgreSQL and MySQL, and 1 and 0 for T-SQL and SQLite, so the SQL runs without errors on each. Strings that say "true" are still text, so they stay quoted.

Are large numbers rounded?

No. JSON numbers are copied exactly as you typed them, so a 20 digit ID such as 12345678901234567890 is written the same. A number that you wrote as a string, such as "007", stays quoted text.

How do I set the table name?

Pick one from the Table name list, or put a first line such as -- table: dbo.Customers above the JSON. That line is read as a setting, not data, and overrides the list. The name can be table or schema.table.

About this tool

Expects an array of flat objects, one per row (a single object gives one row). Columns are the union of all keys in the order first seen, and a key missing from an object becomes NULL. Strings are single-quoted with quotes doubled (and backslashes doubled for MySQL); JSON numbers are written exactly as you typed them, so large integers are not rounded; null becomes NULL; true and false become TRUE and FALSE, or 1 and 0 for T-SQL and SQLite. A nested object or array is stored as JSON text in a quoted string. To use another table name, put a first line such as -- table: dbo.Customers above your data; it overrides the Table name list. Names are quoted for the dialect, T-SQL text with non-ASCII characters is written as N'...', and SQL Server output is capped at 1,000 rows per INSERT. It does not create the table or choose column types. Check the output before running it on a live database.

Format Code Now: free, no ads, no accounts. Your input stays in your browser.