Insert multiple records in database from one form

Question

I'm developing a tracking application - it assigns "taskings"
to divisions and people.

I need a way to take a form and replicate all the data into
seperate records in our database. I know insert will only put one
record in at a time, so it has to be looped. I'll route the records
into to a staging area so that the data can be modified for the
individual divisions - 
which I need no help with.

I want the user to be able to define how many records they will
make, like a textfield saying I want this done 4 times. How would I
write this loop - I'm assuming I should write an index loop over a
query, but I'm thinking CF won't like this. Any
suggestions?

Dan_Bracuk · Answer

How you do your loop depends on the db you are using. MySql support syntax that vaguely resembles this:

insert into mytable
(field1, field2)
values
(value1, value2), (value3, value4)

MS Sql and sybase allow more than one sql statements inside a cfquery tag, so you simply do this
<cfquery>
<cfloop>
sql
closing tags.

There are two generic ways that will always work. The most efficient way depends on your db, so you have to test both methods. The first way is to put your cfquery tag inside your loop. The second is to do something like this

<cfquery>
insert into mytable
(field1, field2)
<cfloop>
select disinct value1, value2 from some_small_table
<cfif not done yet>
union
closing tags

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded