Convert query in loop into 1 statement

I have a query below that takes values from an excel sheet and then inserts them into my table. I would like to collect the data first and then only run 1 insert statement so that this doesn't loop several times to perform the inserts.

Can someone assist with converting this?

	    <cfloop query="data" startRow="2">
      <!--- get values from each column in sheet --->
		<cfset variables.name = data.name[currentRow] />
		<cfset variables.address = data.address[currentRow] />
		<cfset variables.Datebegan = data.Datebegan[currentRow] />
		<cfset variables.ident = data.ident[currentRow] />
		<cfset variables.roomnumber = data.roomnumber[currentRow] />
	
      <!--- insert values into sql table ---->
      <cfquery name="newrow" datasource="#datasource#" >
      INSERT INTO MyTable ( name, address, Datebegan, ident, roomnumber) 
	VALUES ( 	<cfqueryparam value="#variables.name#" cfsqltype="cf_sql_varchar">
	,<cfqueryparam value="#variables.address#" cfsqltype="cf_sql_varchar">
	,<cfqueryparam value="#variables.Datebegan#" cfsqltype="cf_sql_varchar">
	,<cfqueryparam value="#variables.ident#" cfsqltype="cf_sql_varchar">
	,<cfqueryparam value="#variables.roomnumber#" cfsqltype="cf_sql_varchar">
	) 
	</cfquery>
    </cfloop>

Open in new window

earwig75Asked:
Who is Participating?
 
_agx_Commented:
The only way to increase the speed on the CF side is do multiple inserts within the same cfquery tag.  
     <cfquery ....>
        INSERT INTO MyTable ( name, address, Datebegan, ident, roomnumber)
       VALUES
          <cfloop query="data">
                 (       
                   <cfqueryparam value="#variables.name#" cfsqltype="cf_sql_varchar">
                  ,<cfqueryparam value="#variables.address#" cfsqltype="cf_sql_varchar">
                , ... etc
               )
              <cfif currentRow lt data.recordCount>, </cfif>
          </cfloop>
      </cfquery>

Keep in mind there are limits. If it's a ton of values, might want to break it into batches of 500 or so.
0
 
_agx_Commented:
You can't avoid looping in this specific case. You can execute multiple INSERT statements within a single cfquery

               <cfquery ...>
                   INSERT INTO T1 (...)VALUES(...)
                   INSERT INTO T1 (...)VALUES(...)
                   INSERT INTO T1 (...)VALUES(...)
                </cfquery>

... or with some db's (MySQL, MS SQL 2008+, ..) generate multiple VALUES clauses.

                <cfquery ...>
                 INSERT INTO T1 (....) VALUES (....), VALUES(...), VALUES(....)
                </cfquery>

But in your scenario you must still use a CFLOOP to construct that SQL in the first place.
0
 
earwig75Author Commented:
I am fine with looping the first/constructing the first query but would like to make the 2nd as efficent as possible. I am using MS SQL 2008.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
earwig75Author Commented:
I ended up doing it like this:

<CFSET MYSTMT = ''>
<CFSET CRLF = Chr(13) & Chr(10)>
<CFLOOP QUERY="data" startRow="2">

	<CFSET MYSTMT = MYSTMT & "INSERT INTO MyTable
	(name, address, datebegan, ident, roomnumber)
	VALUES ('#name#', '#address#',
	'#datebegan#', '#ident#', '#roomnumber#')#CRLF#;">
    </cfloop>
	
<CFQUERY NAME="InsertData" 
	DATASOURCE="#datasource#">
	#PreserveSingleQuotes(MYSTMT)#
</CFQUERY>		
		

Open in new window

0
 
_agx_Commented:
Oh no - please don't do that! Using preserveSingleQuotes may seem convenient. But it's very dangerous. It opens your db to sql injection, so someone could potentially delete your entire db!  Always use cfqueryparam.
0
 
earwig75Author Commented:
Can I use cfqueryparam with that method? I tried and it didn't seem to work.
0
 
_agx_Commented:
Nope, you can't use cfqueryparam with preserveSingleQuotes.  That's why I avoid it at all costs because there's no bullet proof way to protect the queries.
0
 
earwig75Author Commented:
Selecting the method I used as well since it works.
0
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.