Best way to insert multiple inserts rows to SQL Server using PHP

Question

I'm working in a syncronization task to upload/download data from one SQLServer2014 of client machine database to another SQLServer2014 in a online central server to insert around 36k - 50k rows each time to update/insert/delete data.

The process is initialized by the user in a desktop application and sending in to a web application, a function creates a JSON file containing all database tablenames with respective columns and values. Note that columns and values only are written in JSON file if they are not empty or null. e.g:

{"SCHOOL":[],"PERSON":[{"PERSON_ID":1,"NAME":"PAUL","FUNCTION_ID":1},{"PERSON_ID":2,"NAME":"JOHN","AGE":20, "FUNCTION_ID":3}], "FUNCTION":["FUNCTION_ID":1,"NAME":"TEACHER"}

Code process

A PHP class reads the JSON file getting all tablenames that have contents to import and converts into an array normalizing it to entities names to mapped in web app PHP Doctrine Classes correspondents. (I do this process to see if the table is mapped in doctrine (web app) and check each type in database e.g: int database type must be converted to integer in PHP, varchar must be converted to strings...)

e.g:

SCHOOL => School, PERSON => Person, FUNCTION => Function

For each tablename coming from desktop app found in Doctrine mapped info class at web app, a PHP object (generic class containing dependencies, fields and types, etc) is generated and stored in an array.

e.g:

[Person] => \Entity\Sinc Object
        (
            [fields] => Array
                (
                    [PERSON_ID] => int
                    [NAME] => varchar(max)                    
                )

            [dependencies] => Array
                ......

After converting every table and fields from JSON data to entities names and types of doctrines I generate all inserts to a temp table for each JSON data.

e.g.

"PERSON":[{"PERSON_ID":1,"NAME":"PAUL","FUNCTION_ID":1} turns into 
CREATE TABLE tempdb..#tmpPerson(PERSON_ID int NULL, NAME varchar(max) NULL, AGE int NULL, FUNCTION_ID int NULL)

INSERT INTO tempdb..#tmpPerson(PERSON_ID, NAME, FUNCTION_ID) VALUES (1,"PAUL", 1);
INSERT INTO tempdb..#tmpPerson(PERSON_ID, NAME, AGE, FUNCTION_ID) VALUES (2,"JOHN", 20, 3);

Using tempDb to execute every operation sub-selects that I need before migrating data

Note: If a column is empty in JSON file, it won't appear in insert command.

I am storing every insert into an array and running a foreach to execute it

//$entityManager from Doctrine... sqlInsertsArray contains every insert generated delimiteds with ;
function runInserts($entityManager, $sqlInsertsArray){
    $errors = array();
    foreach($sqlInsertsArray as $key => $sql){                        
            try{                
                $stmt = $entityManager->getConnection()->prepare($sql);                
                $stmt->execute();
                $stmt->closeCursor();
            }catch(Exception $e){                
                $errors[$key] = $e;
            }
    }

    $entityManager->flush();
    $entityManager->clear();
    return $errors;
}

Questions:

What is the best way to run/insert these data into database?
Running every SQL line like this is really slow and expending too
many time depending on data size.
Should I archive every SQL into a SQL file e try another way?
Is there a way to execute batch insert with different columns?

Every additional tips are welcome. Main goals are performance and low cost, or one of then.

At moment I have no way to change the desktop app of client, but I can suggest changes.

Best way to insert multiple inserts rows to SQL Server using PHP

0 Answers