Introduction

MySQL does not provide a single built-in command that turns an existing row into a ready-made INSERT statement. In practice, you either generate the statement yourself with SQL string functions, or you use tooling such as mysqldump when the goal is migration, backup, or copying data between environments.

Generate an INSERT statement with SQL

For a known table and a small number of columns, you can build the statement directly with CONCAT and QUOTE.

1SELECT CONCAT(
2    'INSERT INTO users (id, name, email) VALUES (',
3    id, ', ',
4    QUOTE(name), ', ',
5    QUOTE(email),
6    ');'
7) AS insert_sql
8FROM users
9WHERE id = 42;

QUOTE is important for strings because it escapes quotes and wraps the value correctly. Without it, generated SQL breaks on names such as O'Reilly.

Handle NULL values explicitly

QUOTE(NULL) returns NULL, which is not the same as the literal text you want in the generated statement. Use IFNULL or CASE so the output contains the SQL keyword NULL when appropriate.

1SELECT CONCAT(
2    'INSERT INTO users (id, name, email) VALUES (',
3    id, ', ',
4    IF(name IS NULL, 'NULL', QUOTE(name)), ', ',
5    IF(email IS NULL, 'NULL', QUOTE(email)),
6    ');'
7) AS insert_sql
8FROM users
9WHERE id = 42;

That pattern becomes essential once nullable columns are involved.

Use mysqldump when the goal is export or migration

If the real requirement is "give me SQL I can replay elsewhere," mysqldump is often the better tool. It already knows how to generate valid INSERT statements.

mysqldump -u app_user -p mydb users --where="id = 42" --no-create-info

This is usually safer than hand-building SQL for wide tables because the tool handles column ordering, escaping, and data formatting.

Wide tables are better handled with generated SQL or tooling

Hand-writing a CONCAT expression for a table with dozens of columns is hard to maintain. In those cases, you can query information_schema.columns to generate the generator query, but at that point a dump tool or an application script is often simpler.

If you still need to do it in SQL, ensure the column list in the INSERT matches the generated values exactly. Depending on implicit column order is fragile and breaks as the schema evolves.

Beware of data types that need special treatment

Numbers are easy, but dates, timestamps, binary values, JSON columns, and blobs need care. Some data types need quoting, some should not, and binary data may be easier to export with tooling than with manual SQL assembly.

For example, string-style quoting is appropriate for date and datetime values because MySQL accepts them as string literals:

1SELECT CONCAT(
2    'INSERT INTO audit_log (id, created_at) VALUES (',
3    id, ', ',
4    IF(created_at IS NULL, 'NULL', QUOTE(created_at)),
5    ');'
6)
7FROM audit_log
8WHERE id = 10;

But once blobs or complex binary payloads enter the picture, SQL generation becomes cumbersome quickly.

Use application code when repeatability matters

If you need this operation repeatedly, application code is often the clearest option. A script can read row data, format each column properly, and write reproducible output with tests around it. That is easier to version and safer than copy-pasting one-off SQL fragments from old shell history.

Common Pitfalls

• Building the INSERT text without QUOTE, which breaks on strings containing quotes or special characters.
• Forgetting to handle NULL values explicitly and generating invalid SQL.
• Omitting the column list and relying on current table column order.
• Using hand-built SQL for wide tables when mysqldump would be simpler and safer.
• Assuming every data type can be handled with the same quoting rule.

Summary

• MySQL does not have a one-command built-in feature for this exact task.
• Use CONCAT and QUOTE for small, targeted SQL generation.
• Handle NULL explicitly so the output is valid SQL.
• Prefer mysqldump --no-create-info --where=... for export and migration use cases.
• Be extra careful with wide tables and complex data types.