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