INSERT INTO MyTable(ID,Col1,Col2,Col3) VALUES (100,'some vlaue','some value','2018-10-20');
So the question is "How do we get the insert query statement for this easily ?"
Here I am gonna write a simple PHP function by that you can get the insert query statement for all rows in a table :-
<?php
function makeInsertQuery($mysqli,$table, $where=null) {
$sql="SELECT * FROM `{$table}`".(is_null($where) ? "" : " WHERE ".$where).";";
$result=$mysqli->query($sql);
$fields=array();
foreach ($result->fetch_fields() as $key=>$value) {
$fields[$key]="`{$value->name}`";
}
$values=array();
while ($row=$result->fetch_row()) {
$temp=array();
foreach ($row as $key=>$value) {
$temp[$key]=($value===null ? 'NULL' : "'".$mysqli->real_escape_string($value)."'");
}
$values[]="(".implode(",",$temp).")";
}
$num=$result->num_rows;
return "INSERT `{$table}` (".implode(",",$fields).") VALUES \n".implode(",\n",$values).";";
}
?>
When you pass tableName to @makeInsertQuery($tableName) function. You will get below output..
INSERT INTO `tableName` (`id`, `parent_id`, `name`, `type`) VALUES
(1103, 1019, 'ios11.2.2', 'os'),
(1104, 1019, 'ios11.2.5', 'os'),
(1105, 1017, 'iosos_version', 'os'),
(1106, 1019, 'ios11.0.1', 'os'),
(1107, 1018, 'ios10.2.1', 'os'),
(1108, 1019, 'ios11.1.1', 'os'),
(1109, 1025, 'ios9.1', 'os'),
(1110, 1019, 'ios11.0.3', 'os'),
(1111, 1019, 'ios11.2.1', 'os'),
(1112, 1019, 'ios11.1.2', 'os'),
(1113, 1018, 'ios10.3.1', 'os'),
(1114, 1018, 'ios10.3.3', 'os'),
(1115, 1019, 'ios11.2.6', 'os'),
(1116, 1019, 'ios11.2', 'os'),
(1117, 1018, 'ios10.1.1', 'os');
So using above function you can get insert query statement for a table in MySql.
You can also get insert query statement by dumping or exporting the table into .sql file. So you can dump your table data with executing below command on terminal.
sudo mysqldump -u USERNAME -p DATABASE tableName > tableName.sql;
Windows users can export from MySql workbench:-
If you open the .sql file into editor, you will find query statement same as above. So just copy the all insert query statement and execute where you want.
.png)
.png)
Note: Only a member of this blog may post a comment.