Mehrere Inserts bei MSSQL, MySQL und Postgres

2 Kommentare
Mischa Sameli, Geschäftsführer & Leiter Entwicklung

Immer wieder gesucht und immer wieder benötigt: wie kann ich mit einer Datenbank-Abfrage mehrere Insert-Statements absetzen? Hier drei Varianent für MSSQL, MySQL und PostgreSQL.

Ein multiples Insert-Query ist in der Regel dann gefragt, wenn es gilt, eine Join-Table mit zig Einträgen zu befüllen. Beispielsweise, wenn haufenweise Werte von Checkboxen aus einem Formular verarbeitet werden könnnen. Eine billige Variante mit ColdFusion ist bekanntermassen:

1<cfloop collection="#form#" item="field">
2 <cfquery name="qryInsert" datasource="#myDSN#">
3 INSERT INTO [tablename] (root_ID, join_ID)
4 VALUES (
5 <cfqueryparam cfsqltype="cf_sql_integer" value="#root_ID#">,
6 <cfqueryparam cfsqltype="cf_sql_integer" value="#field#">
7 )
8 </cfquery>
9</cfloop>

Die Methode funktioniert, und zwar für alle Datenbanken. Nur leider ist sie nicht sehr effizient, da für jeden Eintrag eine neue Datenbank-Abfrage ausgelöst wird seitens ColdFusion. Effizienter wird die Geschichte, wenn wir den Schleife innerhalb des cfquery-Tags durchführen:

1<cfquery name="qryInsert" datasource="#myDSN#">
2 <cfloop collection="#form#" item="field">
3 INSERT INTO [tablename] (root_ID, join_ID)
4 VALUES (
5 <cfqueryparam cfsqltype="cf_sql_integer" value="#root_ID#">,
6 <cfqueryparam cfsqltype="cf_sql_integer" value="#field#">
7 )
8 </cfloop>
9</cfquery>

Diese Variante funktioniert bei MSSQL tadellos; andere Datenbanken zeigen da out-of-the-box nicht ganz so viel Begeisterung. MySQL beispielsweise hätte die Abfrage gerne so formuliert

1<cfset count = 0 />
2<cfquery name="qryInsert" datasource="#myDSN#">
3 INSERT INTO [tablename] (root_ID, join_ID)
4 VALUES
5 <cfloop collection="#form#" item="field">
6 <cfset count = count + 1/>
7 (
8 <cfqueryparam cfsqltype="cf_sql_integer" value="#root_ID#">,
9 <cfqueryparam cfsqltype="cf_sql_integer" value="#field#">
10 )<cfif count LT ListLen(form.fieldnames)>,</cfif>
11 </cfloop>
12</cfquery>

Wie man sieht, können die Werte-Paare in einem Loop geschrieben werden. Die gleiche Technik kann übrigens auch für Postgres-Datenbanken ab der Version 8.2 angewandt werden. Für MSSQL taugt sie allerdings nichts. Obwohl wir ja bereits eine funktionierende Abfrage oben haben für MSSQL, hier dennoch eine weitere Möglichkeit, um zum gleichen Ziel zu kommen:

1<cfset count = 0 />
2<cfquery name="qryInsert" datasource="#myDSN#">
3 INSERT INTO [tablename] (root_ID, join_ID)
4 <cfloop collection="#form#" item="field">
5 <cfset count = count + 1/>
6 SELECT <cfqueryparam cfsqltype="cf_sql_integer" value="#root_ID#">,
7 <cfqueryparam cfsqltype="cf_sql_integer" value="#field#">
8 <cfif count LT ListLen(form.fieldnames)>
9 UNION ALL
10 </cfif>
11 </cfloop>
12</cfquery>

Das sieht zwar im Vergleich zur ersten Version ein wenig komplizierter aus, ist aber im Endeffekt offensichtlich effizienter. Die Analyse im Query-Analizer von MSSQL lieferte in jedem Fall bessere Resultate als der Loop über das gesamte Insert-Statement.