• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

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

0
earwig75
Asked:
earwig75
  • 4
  • 4
2 Solutions
 
_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
 
_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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now