0

I have to insert single set of data multiple times , say n rows. 

INSERT INTO MyTable VALUES ("John", 123, "US");

Can I insert all n rows in a single SQL statement?

here n is dynamic value n is user input , how to make insert query n times , any idea.

$sql = "INSERT INTO `mytable` VALUES(`field1`,`field2`,`field3`) VALUES ";
$count = 5;
for($i=0;$i<$coutn;$i++)
{
$sql .= " ('john','123','us' )";
}

is this correct way..

Steve Bals
  • 1,949
  • 6
  • 22
  • 32
  • Check this: http://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql . In your case, therefore, it should be something like `INSERT INTO MyTable (col1,col2,col3) VALUES ("John",123,"US"),("John2",123,"US2") [...]` – briosheje Apr 23 '14 at 09:00
  • You want to insert *the same data* multiple times? Why? Why not simply include an additional column `number_of_times`? – eggyal Apr 23 '14 at 09:02

1 Answers1

2

Yep, this can be done easily, it should look something like this:

INSERT INTO MyTable VALUES ("John", 123, "US"), ("Carl", 123, "EU"), ("Jim", 123, "FR");

However, it is good programming practice to specify the columns of your table in the query, for example:

INSERT INTO MyTable (Column1, Column2, Column3) 
VALUES ("John", 123, "US"), ("Carl", 123, "EU"), ("Jim", 123, "FR");

EDIT : You can build your query like this (in for cycle), the $total is your user input:

$sql = "INSERT INTO MyTable (Column1, Column2, Column3) VALUES";

//Build SQL INSERT query
for ($i = 1; $i <= $total; $i++) {
  $sql .= " ($value1, $value2, $value3), ";
}
//Trim the last comma (,)
$sql = rtrim($sql,",");
//Now, the $sql var contains the complex query. 
$result = mysql_query($sql);

As you can see we do not execute the INSERT statement in the loop, but rather we build the SQL query text and then we will execute it in one pass.

Bud Damyanov
  • 30,171
  • 6
  • 44
  • 52