Thanks Russ, Scott@fussionapps has been kind enough to give me a nice code.. The little challenge was that the columns were not fixed and query results are completely dyanmic...
Just posting it here for reference <CFLOOP query="qry_data"> <CFQUERY name="qry_insert" datasource="#request.dsn#"> INSERT INTO SomeTable ( <CFLOOP from="1" to="#Listlen(qry_data.ColumnList)#" index="i"> #qry_data.GetMetaData().GetColumnName(i)# <CFIF !i EQ ListLen(qry_data.ColumnList)>, </CFIF> </CFLOOP>) VALUES ( <CFLOOP from="1" to="#Listlen(qry_data.ColumnList)#" index="i"> <!--- Putting these into variables to make it easier for you to reuse later if needed ---> <CFSET variables.ColumnName = qry_data.GetMetaData().GetColumnName(i)> <CFSET variables.ColumnType = qry_data.GetMetaData().GetColumnTypeName(i)> <CFSET variables.ColumnData = qry_data[qry_data.GetMetaData().GetColumnName(i)][qry_data.currentrow]> <!--- Check Len so we can insert NULLs where needed ---> <CFIF Len(variables.ColumnData)> #variables.ColumnType# <CFSWITCH expression="#variables.ColumnType#"> <!--- Things that need to be wrapped in single quotes(add as needed) ---> <CFCASE value="VARCHAR,NVARCHAR,DATETIME,SMALLDATETIME,UNIQUEIDENTIFIER"> '#variables.ColumnData#' </CFCASE> <!--- You can add other cases here if you want ---> <!--- Things that dont ---> <CFDEFAULTCASE> #variables.ColumnData# </CFDEFAULTCASE> </CFSWITCH> <CFELSE> NULL </CFIF> <CFIF !i EQ ListLen(qry_data.ColumnList)>, </CFIF> </CFLOOP>) </CFQUERY> </CFLOOP> </CFOUTPUT> Note: small corrections may be required to the code. -----Original Message----- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: Sunday, March 11, 2012 1:48 PM To: cf-talk Subject: Re: CFQuery to Database if its just a few records then use query of query to get your subset of results and then insert the results into database B if this is a regular occurrence and happens often then then you may want to look at creating a DTS script on your SQL server and scheduling that to do the job. On Sat, Mar 10, 2012 at 10:32 PM, IT (Pradeep Viswanathan) < prade...@emiratesnbd.com> wrote: > > I have couple of records which are filtered from data source x which > is oracle based and require to insert it into datasource y which is MS > SQL server. > > The number of rows, columns is not fixed, however I have the records > in a Query object, what would be the best way to insert get this data > into the other database? > > Thanks in advance. > > > DISCLAIMER: > "This e-mail message including any of its attachments is intended > solely for the addressee(s) and may contain privileged information. If > you are not the addressee or you have received this email message in > error, please notify the sender who will remove your details from its > database. You are not authorized to read, copy, disseminate, > distribute or use this e-mail message or any attachment to it in any > manner and must delete the email and destroy any hard copies of it. > This e-mail message does not contain financial instructions or > commitments of any kind. Any views expressed in this message are those > of the individual sender and do not necessarily reflect the views of > Emirates NBD PJSC, or any other related subsidiaries, entities or persons." > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:350350 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
