Get Insert Statement for existing row in MySQL

Question

Using MySQL I can run the query   SHOW CREATE TABLE MyTable    And it will return the create table statement for the specificed table  This is useful if you have a table already created  and want to create the same table on another database     Is it possible to get the insert statement for an already existing row  or set of rows   Some tables have many columns  and it would be nice for me to be able to get an insert statement to transfer rows over to another database without having to write out the insert statement  or without exporting the data to CSV and then importing the same data into the other database   Just to clarify  what I want is something that would work as follows   SHOW INSERT Select   FROM MyTable WHERE ID   10    And have the following returned for me   INSERT INTO MyTable ID Col1 Col2 Col3  VALUES  10  hello world   some value   2010-10-20

User · Answer

you can use Sequel pro to do this  there is an option to  get as insert statement  for the results obtained

User · Answer

You can create a SP with the code below - it supports NULLS as well   select  my table name  into  tableName     find column names   select GROUP CONCAT column name SEPARATOR       from information schema COLUMNS where table schema  DATABASE   and table name    tableName group by table name into  columns      wrap with IFNULL   select replace  columns       IFNULL    into  selectColumns  select replace  selectColumns   IFNULL        NULL     IFNULL    into  selectColumns   select concat  IFNULL    selectColumns      NULL       into  selectColumns     RETRIEVE COLUMN DATA FIELDS BY PK   SELECT   CONCAT       SELECT CONCAT WS                           selectColumns         AS all columns FROM    tableName    where id   5 into  values         INTO  sql   PREPARE stmt FROM  sql  EXECUTE stmt     Create Insert Statement   select CONCAT  insert into    tableName         columns     values       values        into  prepared     UNWRAP NULLS   select replace  prepared     NULL      NULL   as statement

User · Answer

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

User · Answer

In PHPMyAdmin you can    click copy on the row you want to know its insert statements SQL       click Preview SQL       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

User · Answer

I think that the answer provided by Laptop Lifts is best   but since nobody suggested the approach that I use  i figured i should chime in   I use phpMyAdmin to set up and manage my databases most of the time   In it  you can simply put checkmarks next to the rows you want  and at the bottom click  Export  and chose SQL   It will give you INSERT statements for whichever records you selected   Hope this helps

User · Answer

I use the program SQLYOG where I can make a select query  point at the results and choose export as sql  This gives me the insert statements

User · Answer

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

User · Answer

With PDO you can do it this way    stmt   DB  getDB  - gt query  SELECT   FROM sometable   array       array    stmt- gt fetchAll PDO  FETCH ASSOC        fields   array keys  array 0          statement    INSERT INTO user profiles copy    implode      fields     VALUES         statement values   null       foreach   array as  key   gt   post            if isset  statement values                  statement values        n                       values   array values  post           foreach  values as  index   gt   value                 quoted   str replace          str replace            value                 values  index      isset  value     NULL           quoted                            statement values        implode      values                    statement     statement values             echo  statement

User · Answer

There doesn t seem to be a way to get the INSERT statements from the MySQL console  but you can get them using mysqldump like Rob suggested  Specify -t to omit table creation   mysqldump -t -u MyUserName -pMyPassword MyDatabase MyTable --where  ID   10

User · Answer

Laptop Lift s code works fine  but there were a few things I figured people may like   Database handler is an argument  not hardcoded  Used the new mysql api  Replaced  id with an optional  where argument for flexibility  Used real escape string in case anyone has ever tried to do sql injection and to avoid simple breakages involving quotes  Used the INSERT table  field     VALUES  value        syntax so that the fields are defined only once and then just list off the values of each row  implode is awesome   Because Nigel Johnson pointed it out  I added NULL handling   I used  array  key  because I was worried it might somehow change  but unless something is horribly wrong  it shouldn t anyway    lt  php function show inserts  mysqli  table   where null         sql  SELECT   FROM    table     is null  where           WHERE    where            result  mysqli- gt query  sql         fields array        foreach   result- gt fetch fields   as  key  gt  value             fields  key      value- gt name                 values array        while   row  result- gt fetch row               temp array            foreach   row as  key  gt  value                 temp  key    value   null    NULL         mysqli- gt real escape string  value                           values       implode      temp                  num  result- gt num rows      return  INSERT    table      implode      fields     VALUES  n  implode    n   values           gt

User · Answer

I wrote a php function that will do this   I needed to make an insert statement in case a record needs to be replaced after deletion for a history table   function makeRecoverySQL  table   id           get the record                selectSQL    SELECT   FROM       table      WHERE  id         id              result   mysql query  selectSQL   YourDbHandle        row   mysql fetch assoc  result          insertSQL    INSERT INTO       table      SET        foreach   row as  field   gt   value             insertSQL            field              value                     insertSQL   trim  insertSQL              return  insertSQL

User · Answer

Within MySQL work bench perform the following    Click Server   Data Export In the Object Selection Tab select the desired schema  Next  select the desired tables using the list box to the right of the schema  Select a file location to export the script  Click Finish  Navigate to the newly created file and copy the insert statements

User · Answer

Since you copied the table with the SQL produced by SHOW CREATE TABLE MyTable  you could just do the following to load the data into the new table   INSERT INTO dest db dest table SELECT   FROM source db source table    If you really want the INSERT statements  then the only way that I know of is to use mysqldump http   dev mysql com doc refman 5 1 en mysqldump htm  You can give it options to just dump data for a specific table and even limit rows

User · Answer

Based on your comments  your goal is to migrate database changes from a development environment to a production environment   The best way to do this is to keep your database changes in your source code and consequently track them in your source control system such as git or svn   you can get up and running quickly with something like this  https   github com davejkiger mysql-php-migrations  as a very basic custom solution in PHP  you can use a function like this   function store once  table   unique fields   other fields array           where            values   array        foreach   unique fields as  k   gt   v            if   empty  where    where       amp  amp              where      k              values      v             records   query  SELECT   FROM  table WHERE  where    values       if  false     records            store  table  array merge  unique fields   other fields              then you can create a migration script which will update any environment to your specifications

User · Answer

There is a quite easy and useful solution for creating an INSERT Statement for editing without the need to export SQL with just Copy  amp  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  quot Apply quot  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

User · Answer

In MySQL Workbench you can export the results of any single-table query as a list of INSERT statements  Just run the query  and then      click on the floppy disk near Export Import above the results give the target file a name at the bottom of the window  for Format select SQL INSERT statements click Save click Export

[mysql] Get Insert Statement for existing row in MySQL

Examples related to mysql

Examples related to syntax