Get count of inserts on cfquery

I know this is not the most secure method of writing multiple records but for now I am stuck with it in our secure environment.

Using the below sample - I would like a way to somehow relate all of the records that are inserted with the "Insertstuff" query. In other words; if the Insert is run 5 times I'd like a way to know that those 5 records were all submitted at the same time by a person...with this query. Could someone assist with the code or offer a recommendation?

<CFLOOP QUERY="data" startRow="2">

<CFSET sqlins = sqlins & "INSERT INTO myTable
	(col1, col2, col3)

VALUES ('#col1#', '#col2#',


<CFQUERY NAME="Insertstuff"

Open in new window

Who is Participating?
Easiest would be create a single UUID ie createUUID() before the insert. Then insert the UUID in all of the records.  That means using a varchar column, not int, but then you don't have to worry about generating a unique INT value.

Another approach I've used is create a secondary table that stores more details about the action. It comes in handy for reporting because you have access to more details than just "the same user created these records".

The secondary table [UserAction] usually has these columns

         UserActionID - (identity) Unique record id
         UserID - identifies the user performing the action (userID, IP Address, ..whatever)
         ActionDate - Date and time action was performed
         ActionType - (Optional) Indicates action

Before inserting the 5 records, I add a record to the UserAction table and use the "result" attribute to grab the new record ID.

       <cfquery result="userAction" ...>
        INSERT INTO UserAction (UserID, ActionDate, ActionType)
        VALUES (
                   <cfqueryparam value="#theCurrentUser#" ...>
                  , getDate()
                  , <cfqueryparam value="#someActionType#" ...>

Then insert that "ID" into the other  table along with the rest of the data.

          INSERT INTO OtherTable (UserActionID, OtherColumns, ....)
                   <cfqueryparam value="#userAction.IDENTITYCOL#" cfsqltype="cf_sql_integer">
                  , .... rest of values

Important: As with any multiple insert query, wrap everything in a cftransaction
"Relate" how? Not sure what that means...  There may be a better option. So what is your ultimate goal in plain english?

Also what version of CF (8,9 or 10) and database type (MS SQL)?
earwig75Author Commented:
CF9. The way it is written now, each insert is a single record with a new identity (ID) in the table. I would like a way for each set of inserts to have a secondary ID that all match. So if it inserts 3 records like the below... I'd like one column to have the same # so I know that submission was all done at once. In the sample below the first 3 would be 1 submission...

ID     2ndID     col1     col2     col3
1           1            a         b           c
2           1            d         e           f
3           1            g         h           i
4           2            q         v           r
5           3            d         f            l
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Sorry just noticed your code is using PreserveSingleQuotes (ouch). Anyway, you don't *have* to use cfqueryparam. The logic above will work with either. It's just a lot more dangerous without the cfqueryparam ..
earwig75Author Commented:
Please forgive me, I am a bit confused. Using my example since multiple records are written how would I add the same unique ID? The first option you mention sounds viable. Could I create a UUID and then insert the same one in my query that uses the preservesinglequotes?
Just create the UUID before the loop:

      <cfset theUUID = createUUID()>

Then add another value to your insert code. dislaimer, I never ever use PreserveSingleQuotes, so the quoting may be off. Adjust as needed.

<CFSET sqlins = sqlins & "INSERT INTO myTable
      (col1, col2, col3, theNewUUIDColumn)

VALUES ('#col1#', '#col2#', '#col3#' , '#theUUID#' )#CRLF#;">
earwig75Author Commented:
Ok, and with this option I would not need cftransaction correct?
No, you always need cftransaction whenever you're doing related inserts.  It keeps everything together. ie Treats the multiple inserts as one action.  All of them succeed or they all fail.  Otherwise if an error occurs halfway through the inserts, only some of the data will be inserted. You won't know what got inserted and what didn't, and won't be able to retry without creating duplicates...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.