|
It seems your SQL statement is not formed correctly, your $sql should be a string, but you put brackets around it. I suggest you echo out the SQL statements and run manually in your database server and see if you still face the same issue. Likely you may get an error and the loop could not continue and timeout. However, if your data rows are huge, then you should consider bulk insert into, forming all the data values in the while loop and insert only once using one single statement outside the loop,like so:
INSERT INTO projects(name, start_date, end_date)
VALUES
('AI for Marketing','2019-08-01','2019-12-31'),
('ML for Sales','2019-05-15','2019-11-20');
Where the rows after VALUES are the collected data in the while loop to form as value lists for single insert after the While loop. For your case, you can issue one single insert command after the while loop, with all data collected, instead of execute insert in every while loop:
$sql = "";
while($record = $button->getNextSelectedRecord())
{
$sql .= "('" .($record["2"])."','".($record["3"])."','".($record["4"])."','".($record["5"])."',
'".($record["6"])."','".($record["7"])."','".($record["8"])."','".($record["9"])."',
'".($record["10"])."','".($record["11"])."')" ;
// add a comma every value lists
$sql .= ",";
} // Out of the loop, all data collected, remove the last unwanted comma. $sql = rtrim($sql,","); // Form the final SQL statement. $sql = "INSERT INTO copy_1 (2, 3, 4, 5, 6, 7, 8, 9, 10,11) values " . $sql; // execute
CustomQuery($sql);
The size of the SQL statement is limited by SQL server's setting max_allowed_packet in general. If you want to do 10,000 records in one insert, you may have to increase max_allowed_packet or you can also break the single insert into say 100 records per insert, that will still speed up a lot since you need to execute insert only 100 times vs 10,000 times.
|