Get Insert Statement for existing row in MySQL

In PHPMyAdmin you can:

1. click copy on the row you want to know its insert statements SQL:

2. click Preview SQL:

3. you will get the created insert statement that generates it

You can apply that on many rows at once if you select them and click copy from the bottom of the table and then Preview SQl

The below command will dump into the terminal without all the extra stuff mysqldump will output surrounding the INSERT. This allows copying from the terminal without it writing to a file. This is useful if the environment restricts writing new files.

mysqldump -u MyUserName -pMyPassword MyDatabase MyTable --where="ID = 10" --compact --no-create-info --complete-insert --quick

Using mysqldump --help I found the following options.

-q, --quick Don't buffer query, dump directly to stdout. (Defaults to on; use --skip-quick to disable.)

-t, --no-create-info Don't write table creation info.

-c, --complete-insert Use complete insert statements.

--compact Give less verbose output (useful for debugging). Disables structure comments and header/footer constructs. Enables options --skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys --skip-set-charset.

If you want get "insert statement" for your table you can try the following code.

SELECT 
    CONCAT(
        GROUP_CONCAT(
            CONCAT(
                'INSERT INTO `your_table` (`field_1`, `field_2`, `...`, `field_n`) VALUES ("',
                `field_1`,
                '", "',
                `field_2`,
                '", "',
                `...`,
                '", "',
                `field_n`,
                '")'
            ) SEPARATOR ';\n'
        ), ';'
    ) as `QUERY`
FROM `your_table`;

As a result, you will have insers statement:

INSERT INTO your_table (field_1, field_2, ..., field_n) VALUES (value_11, value_12, ... , value_1n);

INSERT INTO your_table (field_1, field_2, ..., field_n) VALUES (value_21, value_22, ... , value_2n);

/...................................................../

INSERT INTO your_table (field_1, field_2, ..., field_n) VALUES (value_m1, value_m2, ... , value_mn);

, where m - number of records in your_table

There is a quite easy and useful solution for creating an INSERT Statement for editing without the need to export SQL with just Copy & Paste (Clipboard):

• Select the row in a query result window of MySQL Workbench, probably even several rows. I use this even if the row does contain different data than I want to insert in my script or when the goal is to create a prepared statement with ? placeholders.
• Paste the copied row which is in your clipboard now into the same table (query results list is an editor in workbench) into the last free row.
• Press "Apply" and a windows opens showing you the INSERT statement - DO NOT EXECUTE
• Copy the SQL from the window to your clipboard
• CANCEL the execution, thus not changing the database but keeping the SQL in your clipboard.
• Paste the SQL wherever you want and edit it as you like, like e.g. inserting ? placeholders

For HeidiSQL users:

If you use HeidiSQL, you can select the row(s) you wish to get insert statement. Then right click > Export grid rows > select "Copy to clipboard" for "Output target", "Selection" for "Row Selection" so you don't export other rows, "SQL INSERTs" for "Output format" > Click OK.

The insert statement will be inside you clipboard.

In case you use phpMyAdmin (Tested on version 5.x):

Click on "Edit" button next to the row for which you would like to have an insert statement, then on the bottom next to the action buttons just select "Show insert query" and press "Go".

You can try this

function get_insert_query($pdo, $table, $where_sth)
{
    $sql = "";
    $row_data = $pdo->query("SELECT * FROM `{$table}` WHERE $where_sth")->fetch();
    if($row_data){
        $sql = "INSERT INTO `$table` (";

        foreach($row_data as $col_name => $value){
            $sql .= "`".$col_name."`, ";
        }
        $sql = rtrim($sql, ", ");

        $sql .= ") VALUES (";

        foreach($row_data as $col_name => $value){
            if (is_string($value)){
                $value = $pdo->quote($value);
            } else if ($value === null){
                $value = 'NULL';
            }

            $sql .= $value .", ";
        }
        $sql = rtrim($sql, ", ");
        $sql .= ");";
    }

    return $sql;
}

To use it, just call:

$pdo = new PDO( "connection string goes here" );
$sql = get_insert_query($pdo, 'texts', "text_id = 959");
echo $sql;

Update for get insert statement for current registers at PhpMyAdmin:

1. Select the table from you DB to get registers from
2. "Export" tab at the top menú as the image below

3. Custom export

4. Move down to "Data creation options"

Once there, select "Insert" function at your preferred syntax

Its very simple. All you have to do is write an Insert statement in a static block and run it as a script as a whole block.

E.g. If you want to get Insert statements from a table (say ENGINEER_DETAILS) for selected rows, then you have to run this block -

spool 
set sqlformat insert
select * from ENGINEER_DETAILS where engineer_name like '%John%';
spool off;

The output of this block will be Insert statements.