Insérer des données calculées avec INSERT … SELECT
Nous avons souvent recours à deux requêtes distinctes pour insérer des données calculées dans une base SQL : d’abord un SELECT, puis un INSERT. Ce schéma fonctionne, mais il fragmente la logique, complique le suivi des traitements, et introduit parfois des risques d’incohérence. Pourtant, MySQL et MariaDB permettent une approche bien plus fluide : insérer directement des données dynamiques en combinant les deux opérations dans une seule instruction.
Dans cet article, nous allons découvrir la syntaxe INSERT INTO ... SELECT ... FROM (...), une construction peu utilisée mais pourtant native, robuste, et puissante. À partir d’un exemple réel issu du projet Allo Agri, nous verrons comment générer automatiquement des noms de fichiers à insérer en base, sans traitement intermédiaire côté PHP. Ce billet prolonge les fondations posées dans Introduction aux Bases de Données Relationnelles et Types de Relations entre Tables dans MySQL/MariaDB, en les mettant en pratique dans un contexte dynamique, clair et reproductible.
Le scénario : insérer un document avec un nom de fichier dynamique
Dans le cadre du projet Allo Agri, chaque déclaration transmise par un utilisateur peut être accompagnée de documents justificatifs. Ces fichiers sont stockés dans une table dédiée, avec pour chaque ligne un nom de fichier structuré, une extension, une date, un identifiant de rattachement.
L’objectif est simple : insérer un nouveau document dans la base de données, avec un nom de fichier généré automatiquement à partir de plusieurs éléments — l’identifiant de la déclaration, une référence unique, et un index correspondant au rang du fichier dans la liste. Ce nom doit être propre, lisible, et garanti sans doublon.
Pour y parvenir, il faut donc compter les documents déjà rattachés à la déclaration, générer un index à partir de ce total, concaténer les différentes informations, et insérer l’ensemble dans la table. Tout cela, si possible, en une seule opération. C’est ce que permet la requête INSERT INTO ... SELECT ... FROM (...) que nous allons décortiquer.
Pourquoi éviter deux requêtes quand une seule suffit
Quand nous devons insérer une donnée calculée dans une table, notre premier réflexe est souvent de la préparer dans une requête SELECT, puis de la réinjecter dans un INSERT. Cela fonctionne, bien sûr, mais cette séparation oblige à manipuler les valeurs en PHP, à stocker temporairement des résultats, et à dupliquer parfois une logique pourtant simple.
// Étape 1 : compter les documents existants pour cette déclaration
$sql_count = "SELECT COUNT(*) FROM tab_documents WHERE ch_doc_decl_id = :decl_id";
$stmt_count = $pdo->prepare($sql_count);
$stmt_count->execute(['decl_id' => $decl_id]);
$doc_index = $stmt_count->fetchColumn() + 1;
// Construction du nom de fichier
$doc_file = str_pad($decl_id, 5, '0', STR_PAD_LEFT) . '-' . $ref . '-' . str_pad($doc_index, 2, '0', STR_PAD_LEFT) . '.' . $type;
// Étape 2 : insertion du document
$sql_insert = "INSERT INTO tab_documents (ch_doc_decl_id, ch_doc_file, ch_doc_type)
VALUES (:decl_id, :doc_file, :type)";
$stmt_insert = $pdo->prepare($sql_insert);
$stmt_insert->execute([
'decl_id' => $decl_id,
'doc_file' => $doc_file,
'type' => $type
]);Ce découpage peut sembler rassurant, surtout dans les phases d’apprentissage, mais il introduit plusieurs risques : des données qui changent entre les deux requêtes, des oublis de vérification, des problèmes de concurrence si plusieurs utilisateurs exécutent l’opération en même temps.
En combinant les deux étapes dans une seule requête SQL, on évite ces effets de bord. On réduit aussi le code PHP nécessaire, on simplifie les logs, et on améliore la lisibilité générale du traitement. Ce n’est pas qu’une question de performance brute : c’est une manière plus sûre, plus stable, et souvent plus élégante d’interagir avec la base.
Nous allons maintenant voir en pratique comment cette approche fonctionne, et pourquoi elle peut rendre nos scripts plus robustes, notamment lorsqu’il s’agit de générer des valeurs dynamiques à la volée.
Comprendre la syntaxe : INSERT INTO … SELECT … FROM
La structure INSERT INTO ... SELECT ... FROM permet de réunir dans une seule requête les données à insérer, même lorsqu’elles sont dynamiques, combinées ou calculées. Cette syntaxe est parfaitement valide en MySQL et MariaDB, et pourtant rarement mise en avant. Elle repose sur une idée simple : ce que nous voulons insérer peut être le résultat d’une requête, y compris une requête qui assemble des morceaux d’informations.
Commençons par ce que nous connaissons bien : insérer une ligne dans une table en SQL repose sur une structure simple.
INSERT INTO ma_table (colonne1, colonne2)
VALUES ('valeur1', 'valeur2');Ici, nous désignons d’abord les colonnes cibles, puis nous précisons, dans le même ordre, les valeurs à insérer. Mais que faire si ces valeurs ne sont pas fixes ? Et si nous voulons les calculer dynamiquement au moment même de l’insertion, à partir d’autres données ?
C’est là qu’intervient une autre forme d’INSERT : au lieu de fournir des VALUES, nous pouvons faire un SELECT. Ce SELECT renverra alors la ou les lignes à insérer.
INSERT INTO ma_table (colonne1, colonne2)
SELECT 'valeur1', 'valeur2';Cela peut sembler étrange au premier abord : pourquoi utiliser SELECT s’il n’y a pas de vraie source de données ? Tout simplement parce que dans SQL, un SELECT ne peut pas exister sans un FROM. Même s’il ne sert qu’à exposer des valeurs littérales ou des calculs, il doit tirer ses données de quelque part.
C’est pourquoi, pour combiner plusieurs données calculées ou transmises dans une même requête, nous allons construire une sorte de table temporaire à la volée, en l’encapsulant dans une sous-requête.
SELECT :valeur1 AS colonne1, :valeur2 AS colonne2
FROM DUAL;Ici, DUAL est une pseudo-table utilisée par convention (notamment en Oracle ou MariaDB) pour permettre un SELECT sans table réelle. Mais cette approche devient vite limitée si nous voulons, par exemple, compter quelque chose à l’intérieur de la même requête, ou assembler plusieurs données.
C’est pourquoi, plutôt que d’écrire un simple FROM DUAL, nous allons créer une sous-requête complète, que nous allons aliaser comme s’il s’agissait d’une table temporaire.
SELECT VIRTUAL_TABLE.colonne1, VIRTUAL_TABLE.colonne2
FROM (
SELECT
:valeur1 AS colonne1,
(SELECT COUNT(*) FROM ...) AS colonne2
) AS VIRTUAL_TABLE;Ce que nous faisons ici, c’est construire une ligne virtuelle contenant plusieurs colonnes : certaines alimentées par des paramètres (transmis en PHP avec PDO), d’autres issues de calculs. Et ce sont ces colonnes-là qui serviront ensuite à remplir notre INSERT.
Contrairement à DUAL, qui est une table fictive mais fixe, ici nous créons notre propre source de données temporaire, que nous nommons librement VIRTUAL_TABLE. Ce nom est arbitraire : il pourrait s’appeler source_temp, doc_data ou tout autre identifiant pertinent. Ce qui compte, c’est qu’il nous permette d’accéder aux valeurs définies à l’intérieur de la sous-requête, comme s’il s’agissait d’une vraie table.
SELECT VIRTUAL_TABLE.colonne1, VIRTUAL_TABLE.colonne2
FROM (
SELECT
:valeur1 AS colonne1,
(SELECT COUNT(*) FROM autre_table WHERE condition_a_respecter) AS colonne2
) AS VIRTUAL_TABLE;C’est donc bien une seule requête SQL, compacte mais puissante, qui construit ses propres données et les insère. Une sorte de tour de passe-passe lisible une fois qu’on a compris qu’un INSERT peut recevoir un SELECT, que ce SELECT doit venir d’un FROM, et que ce FROM peut tout à fait être notre propre sous-requête.
Mais l’un des éléments essentiels de cette table virtuelle n’est pas un simple paramètre : c’est une valeur calculée en direct par une sous-requête imbriquée. Lorsqu’on écrit (SELECT COUNT(*) FROM autre_table WHERE condition_a_respecter) AS colonne2, on demande à MySQL de compter quelque chose à l’intérieur même de la requête, puis de donner à ce résultat un nom.
Pourquoi ne pas écrire simplement un nombre, ou passer un paramètre ? Parce que cette valeur dépend d’un état présent dans la base : ici, le nombre de fichiers déjà enregistrés pour une déclaration donnée. Cette information n’est pas statique — elle évolue à chaque insertion — et doit donc être recalculée à chaque exécution.
Ce COUNT(*) s’inscrit donc dans une logique métier : en comptant les lignes existantes selon un critère (comme WHERE ch_doc_decl_id = :decl_id), nous obtenons ainsi un index qui servira à nommer le fichier suivant. C’est ce mécanisme précis qui rend possible la génération d’un nom de fichier structuré et unique, sans passer par une requête préalable. En une seule ligne, nous savons combien d’éléments sont déjà là — et quel sera le prochain.
En regroupant le tout, on obtient la syntaxe complète :
INSERT INTO ma_table (colonne1, colonne2)
SELECT VIRTUAL_TABLE.colonne1, VIRTUAL_TABLE.colonne2
FROM (
SELECT
:valeur1 AS colonne1,
(SELECT COUNT(*) FROM autre_table WHERE condition_a_respecter) AS colonne2
) AS VIRTUAL_TABLE;Étude de cas : générer automatiquement un nom de fichier
Pour ancrer tout ce que nous venons de voir, prenons un cas concret. Nous voulons insérer dans une table tab_documents un nouveau document lié à une déclaration. Ce document possède trois informations essentielles :
– l’identifiant de la déclaration (ch_doc_decl_id)
– une référence qui servira à nommer le fichier (ref)
– et le type de fichier (par exemple : pdf, jpg, etc.)
Étape 1 : insérer uniquement des valeurs transmises
Commençons simplement, en transmettant des données via des paramètres. Pas de calcul, pas de concaténation.
INSERT INTO tab_documents (
ch_doc_decl_id,
ch_doc_file,
ch_doc_type
)
SELECT
VIRTUAL_TABLE.decl_id,
VIRTUAL_TABLE.ref,
VIRTUAL_TABLE.type
FROM (
SELECT
:decl_id AS decl_id,
:ref AS ref,
:type AS type
) AS VIRTUAL_TABLE;Ici, nous insérons directement les valeurs telles qu’elles nous sont fournies. Cela peut suffire si ref contient déjà un nom de fichier complet. Mais ce n’est pas toujours le cas.
Étape 2 : ajouter une valeur calculée en temps réel
Nous voulons maintenant attribuer à chaque nouveau fichier un index unique, en fonction de son rang parmi les documents déjà liés à la déclaration. Pour cela, nous comptons les enregistrements existants et ajoutons 1 au total, directement dans la sous-requête au moment de l’insertion.
SELECT
:decl_id AS decl_id,
:ref AS ref,
:type AS type,
(SELECT COUNT(*) + 1
FROM tab_documents
WHERE ch_doc_decl_id = :decl_id) AS doc_indexÉtape 3 : construire dynamiquement le nom de fichier
Nous allons maintenant combiner ces éléments pour générer un nom de fichier structuré, dans le SELECT principal. C’est ici qu’interviennent les fonctions CONCAT() et LPAD() pour forcer des formats homogènes.
SELECT
VIRTUAL_TABLE.decl_id,
CONCAT(
LPAD(VIRTUAL_TABLE.decl_id, 5, '0'), '-',
VIRTUAL_TABLE.ref, '-',
LPAD(VIRTUAL_TABLE.doc_index, 2, '0'), '.',
VIRTUAL_TABLE.type
),
VIRTUAL_TABLE.type
FROM (
SELECT
:decl_id AS decl_id,
:ref AS ref,
:type AS type,
(SELECT COUNT(*) + 1
FROM tab_documents
WHERE ch_doc_decl_id = :decl_id) AS doc_index
) AS VIRTUAL_TABLE;Le résultat de ce CONCAT() sera par exemple :
00042-REF123-01.pdfCe nom est propre, prévisible, et surtout cohérent avec les documents déjà présents. On peut donc insérer le tout dans la table cible, sans risque de doublon ou d’erreur de synchronisation.
Encapsuler la logique dans PHP avec PDO
Une fois la requête SQL bien construite, son intégration dans un script PHP avec PDO ne demande aucune gymnastique particulière. L’ensemble peut être préparé comme n’importe quel INSERT, avec des paramètres sécurisés, et exécuté en une seule fois.
$sql = "
INSERT INTO tab_documents (
ch_doc_decl_id,
ch_doc_file,
ch_doc_type
)
SELECT
VIRTUAL_TABLE.decl_id,
CONCAT(
LPAD(VIRTUAL_TABLE.decl_id, 5, '0'), '-',
VIRTUAL_TABLE.ref, '-',
LPAD(VIRTUAL_TABLE.doc_index, 2, '0'), '.',
VIRTUAL_TABLE.type
),
VIRTUAL_TABLE.type
FROM (
SELECT
:decl_id AS decl_id,
:ref AS ref,
:type AS type,
(SELECT COUNT(*) + 1
FROM tab_documents
WHERE ch_doc_decl_id = :decl_id) AS doc_index
) AS VIRTUAL_TABLE;
";
$stmt = $pdo->prepare($sql);
$stmt->execute([
'decl_id' => $decl_id,
'ref' => $ref,
'type' => $type
]);La requête est entièrement déléguée au moteur SQL. Le code PHP se contente de lui transmettre les paramètres, de façon claire et sécurisée. Aucune manipulation intermédiaire, aucun comptage ou traitement n’a lieu côté PHP : tout repose sur la logique de la base.
Cela réduit non seulement la taille du script, mais aussi le risque d’erreur. On évite les doublons liés à des traitements mal synchronisés, et on s’assure que le nom de fichier est cohérent avec l’état actuel des données.
C’est aussi un gain en robustesse : la logique est centralisée dans une requête unique, réutilisable, testable, et bien plus facile à maintenir que deux requêtes séparées avec des variables transitoires.
Traiter les cas particuliers : NULL, COALESCE, valeurs par défaut
Quand on prépare une requête INSERT ... SELECT, il est facile d’oublier que certains champs peuvent rester vides, ou contenir des valeurs NULL. Si notre table accepte les NULL, cela ne posera pas de problème technique immédiat, mais le comportement en production peut devenir imprévisible.
Prenons un exemple : si la variable :ref est vide ou absente, le nom de fichier généré par le CONCAT() deviendra :
00042--01.pdfCe nom est techniquement valide, mais il est incomplet, peu lisible, et source de confusion. De même, si le champ type est manquant, le fichier inséré pourrait se terminer par un simple point (., sans extension).
Pour éviter ces cas ambigus, on peut utiliser la fonction COALESCE() à l’intérieur même de la sous-requête. Elle permet de substituer une valeur par défaut à tout paramètre qui serait NULL.
COALESCE(:ref, 'UNDEFINED') AS ref,
COALESCE(:type, 'txt') AS typeAvec cette précaution, même si une variable PHP n’a pas été définie, le SQL garantit une valeur de repli. Cela permet de générer un nom de fichier propre :
00042-UNDEFINED-01.txtOn peut également, selon les besoins, ajouter des vérifications côté PHP avant d’exécuter la requête, mais centraliser ces garde-fous dans la requête elle-même évite bien des oublis.
Enfin, attention à ne pas confondre une valeur vide ('') avec NULL : selon la configuration de votre base, les comportements peuvent différer. Il est toujours préférable de contrôler explicitement ce que l’on envoie ou ce que l’on construit.
Pourquoi cette méthode est plus robuste qu’il n’y paraît
À première vue, l’usage de INSERT ... SELECT ... FROM (...) peut sembler un artifice syntaxique, voire une construction réservée aux cas complexes. En réalité, c’est une approche qui renforce considérablement la stabilité et la fiabilité de nos traitements, tout en allégeant notre code.
D’abord, parce que tout se passe en une seule requête, MySQL la traite comme une opération atomique. Il n’y a plus de temps mort entre un SELECT qui calcule une valeur et un INSERT qui s’en sert. Cela évite les effets de bord si deux utilisateurs ou deux processus effectuent la même opération en parallèle.
Ensuite, la lisibilité y gagne : le calcul du nom de fichier, la construction de l’index, l’assemblage final… tout est regroupé, visible, cohérent. Cela rend les traitements plus compréhensibles pour les développeurs qui relisent ou maintiennent le code.
Enfin, cette méthode pousse à séparer les rôles : le code PHP transmet les paramètres, la base les traite. On évite ainsi que le code applicatif fasse de la “petite cuisine” sur les données, au risque d’incohérence ou de redondance. La requête devient, à elle seule, une petite unité métier claire et portable.
Cette approche est donc précieuse bien au-delà de notre exemple de génération de fichier. Elle s’applique à tout traitement qui repose sur une combinaison de données existantes et d’informations dynamiques : génération de numéros, insertion de logs, archivage, création de vues dérivées, etc.
Adapter la requête pour plusieurs insertions en une fois
Jusqu’ici, nous avons vu comment insérer un seul document, avec un nom de fichier construit dynamiquement à partir de l’état de la base. Mais dans certaines situations, plusieurs fichiers doivent être associés à une même déclaration en une seule opération.
Peut-on réutiliser cette approche pour traiter plusieurs documents à la volée ? La réponse est oui, mais avec précaution.
L’idée consiste à étendre la sous-requête pour qu’elle génère non plus une seule ligne, mais plusieurs. On pourrait, par exemple, s’appuyer sur une table temporaire, une jointure sur une liste en mémoire, ou un tableau PHP converti en UNION SELECT. Le problème vient alors du calcul de l’index. Dans notre cas, l’index est obtenu par :
(SELECT COUNT(*) + 1 FROM tab_documents WHERE ch_doc_decl_id = :decl_id)Mais si nous l’utilisons tel quel pour plusieurs lignes, toutes recevront le même index, ce qui provoquerait des doublons de nom de fichier.
Pour contourner cela, il faudrait calculer l’index de départ une seule fois, côté PHP ou dans une variable SQL, puis l’incrémenter ligne par ligne dans la requête. Cela reste possible, mais nécessite un langage SQL plus avancé (variables, fonctions, curseurs…).
En PHP, on peut aussi construire une boucle qui génère et exécute la requête INSERT ... SELECT ... autant de fois que nécessaire, en recalculant l’index à chaque tour, ce qui garantit un comportement correct et un nom unique pour chaque fichier.
Dans ce cas, le cœur de la requête reste inchangé, mais on adapte le code qui pilote l’enchaînement, comme ici :
foreach ($fichiers as $i => $fic) {
$stmt->execute([
'decl_id' => $decl_id,
'ref' => $fic['ref'],
'type' => $fic['type']
]);
}Ce choix a un avantage : il permet de conserver une requête compacte, bien testée, tout en traitant plusieurs fichiers de manière maîtrisée, sans introduire d’instabilité dans la logique SQL.
Conclusion
Insérer des données dynamiques en SQL ne signifie pas forcément multiplier les requêtes, ni déléguer tous les calculs au code PHP. Grâce à la syntaxe INSERT INTO ... SELECT ... FROM (...), nous avons vu qu’il est possible de construire, combiner et injecter des valeurs dans la base en une seule opération lisible, cohérente et robuste.
En structurant une table virtuelle temporaire, en combinant paramètres et calculs comme le COUNT(*), et en encapsulant le tout dans un SELECT bien formé, nous réduisons les effets de bord, clarifions la logique métier, et préparons des traitements plus sûrs à long terme.
Cette approche ne remplace pas toutes les autres, mais elle mérite d’être connue, testée, et intégrée dans notre boîte à outils SQL. Elle rappelle que l’on peut faire confiance au moteur de base de données pour gérer plus que de simples insertions brutes, à condition de lui fournir des requêtes bien construites.
