AllAccepted answers
Robert Cemper · Feb 26, 2018

You look for INSERT from Query
doc is here http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_insert#RSQL_insertselect

As far as I see it should work the way you have written
(assuming data types between target and source match) 

To estimate runtime you may try the select count(*) from  VwSrcTable  first to get a feeling
how many records that will be. 

Then during load running Select count(*) from DestTable from a 2nd session may let you see your progress.
 

Robert Cemper · Feb 22, 2018

SQLCODE -99 Privilege violation

You have 3 different sets of access rights in this scenario

  • your  development user    
  • the application user
  • the user your Caché / ENS server installation is running on

Check if also  your server has the required access rights at OS level.
under *IX it's quite likely that you don't run as root.
(though this happens also on other OS)
 

Robert Cemper · Feb 21, 2018

It was first documented in 2015.2
chapter 12.1.1 p.108
http://docs.intersystems.com/documentation/cache/20152/pdfs/GSQL.pdf

• Dynamic SQL can accept a literal value input to a query in two ways: input parameters specified at execution time
using the “?” character, and input host variables (for example, :var) specified at prepare time. Embedded SQL uses
input and output host variables (for example, :var).

Robert Cemper · Feb 21, 2018

An interesting observation!
IT WORKS !

I just retyped it a little bit extended for fast retry:

set minage = 80
set myquery = 3
set tStatement = ##class(%SQL.Statement).%New()
set myquery(1) = "SELECT top 10 %ID AS id, Age , Name, %ODBCOUT(DOB) DOB, Home_State"
set myquery(2) = "FROM Sample.Person WHERE Age > :minage"
set myquery(3) = "ORDER BY 2"
set qStatus = tStatement.%Prepare(.myquery)
set tResult = tStatement.%Execute()
do tResult.%Display()
in SAMPLES:
id      Age     Name                    DOB     Home_State
16      82      Schaefer,Alvin S.       1935-05-05      HI
108     82      Adams,Brian Q.  1936-02-21      IL
199     82      Yeats,Ashley K. 1935-10-28      NC
74      83      Ravazzolo,Molly I.      1934-12-31      WV
63      84      Cheng,Filomena J.       1933-12-27      NM
69      84      Yeats,Patrick U.        1933-04-19      KY
92      85      Lepon,Liza M.   1932-06-03      MN
94      87      Browne,Patricia I.      1930-04-05      AL
111     87      Orlin,Edward J. 1930-04-10      OR
197     87      Rogers,Barbara M.       1930-12-06      WI

It also works using traditional %ResultSet

Though I didn't interpret nor use it that way it is documented here:

Dynamic SQL versus Embedded SQL    (4th point)

Dynamic SQL can accept a literal value input to a query in two ways:
input parameters specified using the “?” character,
and input host variables (for example, :var).
Embedded SQL uses input and output host variables (for example, :var).
Robert Cemper · Feb 20, 2018

In general I would agree:
Though in order to use the class query you have the change  to namespace "%SYS" and  back.
As the query is predefined you finally build your own lookup list which is a copy of the original global.

Anyhow this would provide the required result:

     kill roles
    set roles=""
    new $namespace
    zn "%SYS"
    set rs=##class(%ResultSet).%New("Security.Roles:ListAll1")
    set tSC=rs.Execute()
    if tSC
       {
        while rs.Next() {
            set role=rs.Get("Name")
            write ">",role,! ;; just for display
            set roles($zcvt(role,"L"))=""
       } }
     set rs=""                       ;; for save return from "%SYS"
     zw roles                        ;; for demo
     set list2=$lfs(input,"CN=")
     for i=2:1:$LL(list2) {
        set CN=$p($li(list2,i),",") ;; get 1st piece
        set CN=$zcvt(CN,"L") ;; lower case required..v
        set exists=''$d(roles(CN))
        write !,i," ",exists," ",CN ;; for debugging and demo
        if exists write " role found"
        }
     quit                            ;; get back to original namespace
Robert Cemper · Feb 19, 2018

what is this ?  a String or a JSON object ,  or a fixed sequence of (CN=  ,OU=,  DC=, DC= )
there is no obvious groupIng by a separator visible.
You may start by

set list=$lfs($p($p(input,"{",2),"}"))
zw list
list=$lb("CN=Access.Ensemble.Developer.User","OU=Access Groups","DC=OSUMC","DC=EDU"," CN=[CPD Admin]","OU=Distribution Lists","DC=OSUMC","DC=EDU"," CN=[MUSE_Access]","OU=Distribution Lists","DC=OSUMC","DC=EDU"," CN=[IT eMaterials]","OU=Distribution Lists","DC=OSUMC","DC=EDU...")

 

But instead of an unstructured string you have an unstructured list  

Robert Cemper · Feb 19, 2018

George, Gordon
Interesting info on Upgrade2016.1  This seems to by some fake news.
Since the class is there om 2017.2 and even in IRIS2018.1
http://docs.intersystems.com/iris20181/csp/documatic/%25CSP.Documatic.cls?PAGE=CLASS&LIBRARY=%25SYS&CLASSNAME=%25CSP.Stream

The Type set fails because %CSP.Stream is just an abstract class. 
Like %CSP.Page you have to create the real object yourself as %CSP.CharacterStream or %CSP.BinaryStream
 

a little bit simplified for retyping in terminal:

set req = ##class(%CSP.Request).%New()
set cont=req.Content zw cont   ; MO AUTOMATIC OBJECT BEAUSE ABSTRACT !!
cont=""
set cont=##class(%CSP.CharacterStream).%New()  zw cont  ; NOW WE HAVE AN OBJECT
cont=<OBJECT REFERENCE>[5@%CSP.CharacterStream]
+----------------- general information ---------------
|      oref value: 5
|      class name: %CSP.CharacterStream
| reference count: 2
+----------------- attribute values ------------------
|     (%Concurrency) = 1
|    (%LastModified) = ""
|          %Location = ""  <Get,Set>
|         (%LockRef) = ""
|          (%Locked) = 0
|              AtEnd = 0
|           (Buffer) = ""
|           (IOSize) = 0
|                 Id = ""
|     LineTerminator = $c(13,10)  <Set>
|        (MaxNodeNo) = 0
|             (Mode) = 0
|           (NodeNo) = 0
|         (Position) = 1
|        (StoreNode) = ""
|        (StoreRoot) = "^CacheStream"
|         (TempNode) = ""
+--------------- calculated references ---------------
|            CharSet   <Get,Set>
|        ContentType   <Get,Set>
|            Expires   <Get,Set>
|           FileName   <Get,Set>
|            Headers   <Get,Set>
|       LastModified   <Get>
|        MimeSection   <Get,Set>
|               Size   <Get>
|        (StoreGlvn)   <Get>
|         (TempGlvn)   <Get>
+-----------------------------------------------------
set cont.ContentType="application/json"

; and so on ....

HTH

Robert Cemper · Feb 19, 2018

Digging around working sets I found them unable to cover package  structures:
packages seem to be implemented as kind of sub-directory. 

You see the same effect in editor pane. The package is only visible in mouseOver event.
  

You only see the packages if you click the down arrow in right upper corner and "Edit Working Set".

For "TZ = TimeZone" I googled around Eclipse and opened a can of worms. With no answer.