Copy GatewayResultSet To Another Table

Hi,

What you can do is a snapshot of this ResultSet.

Then use the global of this snapshot for another Operation :

Get snapshot global :

Method OnGetSnapshot(pRequest As Ens.Request, Output pResponse As Ens.StringResponse) As %Status
{
	set tStatus = $$$OK
	
	try{
		
		set pResponse = ##class(Ens.StringResponse).%New()
				
		set tQuery = "SELECT *  FROM [sqlserver].[dbo].[whatever] "
 
		//$$$TRACE(tQuery)		
		Set pSnap = ##class(EnsLib.SQL.Snapshot).%New()
		
		//size of snapshot
		// -1 = Max
		set pSnap.MaxRowsToGet = -1
				
		$$$ThrowOnError(..Adapter.ExecuteQueryBatch(pSnap,tQuery,1000))
		
		$$$ThrowOnError(pSnap.%Save())
		
	
		set pResponse.StringValue = pSnap.%GblRef	
			
	}
	catch exp
		{
			Set tStatus = exp.AsStatus()
		}
	Quit tStatus
}

Use snapshot global :

Method UseSnapshot(pRequest As Ens.StringRequest, Output pResponse As Ens.Response) As %Status
{
	
	set status = $$$OK
	
	try {
		set pResponse = ##class(Ens.Response).%New()

		set nRow = 0
		set tSequence = 0
		
		//Get SnapShot
		Set tSnap = ##class(EnsLib.SQL.Snapshot).%New()
		set tSnap.%GblRef = pRequest.StringValue // use of global SnapShot
		set tSnap.%CurrentRow = 0
		set tSnap.FirstRow = 1
		set tSnap.MaxRowsToGet = -1
		
		$$$TRACE("MaxRowsToGet :  "_tSnap.RowCountGet())

				while tSnap.Next() {
					try {
							set nRow = nRow + 1
							set tSequence = tSequence + 1

                            set i = 0

                            set i = i + 1
                            set tParam(i) = tSnap.Get("Col1")

                            
                            set i = i + 1
                            set tParam(i) = tSnap.Get("Col2")

                       
                            set i = i + 1
                            set tParam(i) = tSnap.Get("Col3")

                           
                            set i = i + 1
                            set tParam(i) = tSnap.Get("ColX")

                            set tParam = i

							set tQuery = "UPDATE Whatever  "_
										"SET   "_
										"Col1 = ?  "_
										",Col2 = ?  "_
										",Col3 = ?  "_
										" WHERE ColX = ?  " 
														
							
							$$$ThrowOnError(..Adapter.ExecuteUpdateParmArray(.tResult,tQuery,.tParam))

						} catch exSnap {
                            // Update exeption
						}
			
		}
		
		
	} catch ex {
		set status = ex.AsStatus()
	}
	return status
}

Furthermore, If you have big table to query/insert in JDBC consider this ZPM module : https://github.com/grongierisc/BatchSqlOutboundAdapter