Insert more than one record into a table

Question

Currently, I am following the stages of official source of api.

And in the current state of the project, I’m getting table information, changing and entering data, no errors.

However, I would like to do a mass insertion in my table.

This is my code

$array = array();
foreach ($objects as $object) {
    array_push(
        $array,
        "('".$object->lat.",".$object->lng."','".$object->other->lat.",".$object->other->lng."')"
    );
}
$values = implode(",", $array);
$client = new Google_Client();
$tableId = "TableId";
$client->useApplicationDefaultCredentials();
$client->setScopes('https://www.googleapis.com/auth/fusiontables');
$service = new Google_Service_Fusiontables($client);

$service->query->sql("INSERT INTO ".$tableId." ('Location', 'City Location') VALUES ".$values); // I'm sorry, I forgot.

When I try to enter only one record with the same code, it works

This is my variable sql when you have more than one record:

INSERT INTO TableId 
('Location', 'City Location') 
VALUES 
('88.064342,-50.280747','-8.77,-36.62'),
(-55.781345,-69.294770','-28.24,-48.67'),
('14.696452,-26.844802','-19.92,-43.17')

The api returns the following error:

{  
  "error":{  
    "errors":[  
      {  
        "domain":"fusiontables",
        "reason":"badQueryCouldNotParse",
        "message":"Invalid query: Parse error near ',' (line 1, position 92).",
        "locationType":"parameter",
        "location":"q"
      }
    ],
    "code":400,
    "message":"Invalid query: Parse error near ',' (line 1, position 92)."
  }
}

Answer 1

The syntax for inserting more than one record in the google fusion API is another, as can be seen in:

https://developers.google.com/fusiontables/docs/v2/sql-reference#insertRow

INSERT

To insert one or more lines, the syntax HTTP POST request SQL is the following:

INSERT INTO table_id (nome_coluna, nome_coluna2, nome_coluna3) VALUES (valor1, valor2, valor3);
INSERT INTO table_id (nome_coluna, nome_coluna2, nome_coluna3) VALUES (valor1, valor2, valor3);
.
.
.
INSERT INTO table_id (nome_coluna, nome_coluna2, nome_coluna3) VALUES (valor1, valor2, valor3);

---

Note that this syntax is only valid for 500 records, so you can loop your object and insert 500 in 500.

My code to enter, 500 to 500, using the Laravel looked like this:

/* 
Esse método pega de 500 em 500 registros, no caso de a tabela ter uma grande quantidade 
de registros, é a mais viável
*/
$objects = Model::with('other')->chunk(500, function ($objects) { 

    $tableId = "tableId";
    foreach ($objects as $object) {
        $sql = 'INSERT INTO `'.$tableId.'` 
        (`Location`, `Other Location`) 
        VALUES 
        ("'.$object->lat.",".$object->lng.'","'.$object->other->lat.",".$object->other->lng.'");';
    }

    $client = new Google_Client();
    $client->useApplicationDefaultCredentials();
    $client->setScopes('https://www.googleapis.com/auth/fusiontables');
    $service = new Google_Service_Fusiontables($client);
    $service->query->sql($sql);
    sleep(2); // Para dar um tempo ao web service e não sobrecarregar
});

The total data size of an order should not exceed 1MB and the total number of cells in the table being added should not exceed 10,000.

To better elude what a cell is:

That is, it is no use wanting to add a statement with 500 columns, the maximum statments you would be allowed is 20.

If you want to insert an excessive number of lines, the most suitable is to use the import method, which will be faster and more reliable than using many SQL INSERT statements.

---

Even after following the notes and documentation requirements, I noticed that the data insertion was beyond too slow, returning too much api error, indicating that the server is "Busy". So I went to the second option, to use the method importRows.

My table has the following structure:

To get the csv file that will be imported I used the administrative panel of phpmyadmin. I used the following sql to mount the query:

SELECT
    '' AS `Text`,
    '' AS `Number`,
    CONCAT(object.lat, ",", object.lng) AS `Location`,
    '' AS `Date`,
    CONCAT(other.lat, ",", other.lng) AS `City Location`
FROM
    `ads`
INNER JOIN cities ON object.city_id = other.id

I will insert the step by step I followed until I get the csv needed:

After that I put the generated file in a location accessible by my server and ran the following code:

$client = new Google_Client();
$client->useApplicationDefaultCredentials();
$client->setScopes('https://www.googleapis.com/auth/fusiontables');
$service = new Google_Service_Fusiontables($client);
$tableId = "tabelId";
$service->table->importRows(
    $tableId, 
    array(
        'data' => file_get_contents('objects.csv'), 
        'uploadType' => 'media',
        'mimeType' => 'application/octet-stream'
    )
);

AND VOILA! 100,000 records entered into the fusion table successfully:

Answer 2

Mass insertion can be done by using UNION ALL as follows:

INSERT INTO TableId ('Location', 'City Location')  
SELECT '88.064342,-50.280747','-8.77,-36.62'   
UNION ALL 
SELECT '-55.781345,-69.294770','-28.24,-48.67' 
UNION ALL 
SELECT '14.696452,-26.844802','-19.92,-43.17'