How can I split this INSERT in steps?

Question

How can I split this INSERT in steps?

Navigation

#1 by (3 votes)
#2 by (3 votes)
#3 by (3 votes)

1

I have 70MIL records to insert into a table3 from table1 - 4 fields and table2 - 1 field . The following code does exactly what I need in the most optimized way a user has helped me build.

INSERT INTO imagens3 (ID, IMOVEL, CODIGO, IMAGEM_G, IMAGEM_P) 
SELECT t1.ID, t1.IMOVEL, t1.CODIGO, t2.IMAGEM_G, t1.IMAGEM_P 
FROM imagens1 t1 INNER JOIN imagens2 t2 ON t1.ID = t2.ID LIMIT 10000

I need to enter 10MIL at a time to get to the end of my work successfully.

How can I create a statement to insert 10MIL records at a time through php?

php mysql query

asked by anonymous 19.01.2015 / 20:10

3 answers

background image of android screen Query in MySQL query

score 3 · Answer 1

You can use the two parameters allowed in the LIMIT clause, where the first one indicates from which line you want to read (indexed to zero).

So the first query remains the same, saving up to 10,000 records in the source table (images1) to be inserted into the destination table (images3):

... FROM imagens1 t1 INNER JOIN imagens2 t2 ON t1.ID = t2.ID LIMIT 10000

On the second run, you enter two parameters for LIMIT, indicating that you want to return another 10,000 records from the 10,000 row (which was not covered by the previous INSERT).

... FROM imagens1 t1 INNER JOIN imagens2 t2 ON t1.ID = t2.ID LIMIT 10000, 10000

Now you've got the logic; continue until you have rescued and inserted the 70 thousand lines:

... FROM imagens1 t1 INNER JOIN imagens2 t2 ON t1.ID = t2.ID LIMIT 20000, 10000
... 
... FROM imagens1 t1 INNER JOIN imagens2 t2 ON t1.ID = t2.ID LIMIT 30000, 10000
...

score 3 · Answer 2

As already stated in other answers, it is possible through OFFSET :

$inicio = 0;
$limite = 10000;
$total = 70000;

$sql = "INSERT INTO imagens3 (ID, IMOVEL, CODIGO, IMAGEM_G, IMAGEM_P) "
. "SELECT t1.ID, t1.IMOVEL, t1.CODIGO, t2.IMAGEM_G, t1.IMAGEM_P "
. "FROM imagens1 t1 INNER JOIN imagens2 t2 ON t1.ID = t2.ID LIMIT ";

for ($inicio; $inicio <= $total; $inicio += $limite) {
    echo $sql . "{$inicio}, {$limite}\n";
}

See example working on ideone .

score 3 · Answer 3

In PHP, regardless of the number of records in the database:

<?
mysql_connect( ... );
$query = mysql_query("SELECT * FROM imagens1 t1 INNER JOIN imagens2 t2 ON t1.ID = t2.ID");
$num_rows = mysql_num_rows($query);

for($i = 0, $i < $num_rows; $i += 10000) {
  mysql_query(
    "INSERT INTO imagens3 (ID, IMOVEL, CODIGO, IMAGEM_G, IMAGEM_P) 
     SELECT t1.ID, t1.IMOVEL, t1.CODIGO, t2.IMAGEM_G, t1.IMAGEM_P 
     FROM imagens1 t1 INNER JOIN imagens2 t2 ON t1.ID = t2.ID LIMIT 10000 OFFSET #{i}");
}