insert multiple row

[sweetyp2005]

How do i insert multiple rows in database.
for example: I have 3 list:
list1: a1,a2,a3,a4
list2: b1,b2,b3,b4
list3: c1,c2,c3,c4

I want to insert a1,b1,c1 in 1st row. a2,b2,c2 in 2nd row,...
How do I write one sql insert statement to do that?

[mxstu]

sweetyp2005,

It depends on your database. As zoeski80 mentioned, mySql does allow multiple
row inserts in a single cfquery but the syntax is not standard across most
databases (ie. may not be portable). Access does not allow multiple row
inserts in a single cfquery. Some other databases like sql server, etc allow
multiple insert statements in the same query with each statement separated by a
semi-colon.

<cfquery ...>
INSERT INTO someTable (column1, column2, column3)
VALUES ('a1',b1', 'c1');
INSERT INTO someTable (column1, column2, column3)
VALUES ('a2',b2', 'c2');
... etc....
</cfquery>

Verify that all (3) lists contain the same number of elements and then cfloop
from 1 to the total number of elements in the lists. Within each cfloop grab
the list elements at the current loop index position.



<cfset list1 = "a1,a2,a3,a4">
<cfset list2 = "b1,b2,b3,b4">
<cfset list3 = "c1,c2,c3,c4">

<!--- single insert per cfquery method --->
<cfif listlen(list1) gt 0>
<!--- verify lists contain same number of elements --->
<cfif listLen(list1) eq listLen(list2) AND listLen(list2) eq listLen(list3)>
<cfloop from="1" to="#listLen(list1)#" index="j">
<cfquery name="addData" datasource="#yourDSN#">
INSERT INTO someTable (column1, column2, column3)
VALUES ('#listGetAt(list1, j)#', '#listGetAt(list2, j)#', '#listGetAt(list3,
j)#')
</cfquery>
</cfloop>
<cfelse>
Oops! The list sizes don't match.
</cfif>
</cfif>

[SQLMenace]

INSERT INTO someTable
select 'a1',a2', 'a3','a4'
union all
select 'b1',b2', 'b3','b4'
union all
select 'c1',c2', 'c3','c4'