Cache Sql Record set copy to a table

SQL, ZEN, Caché

Hello all,

I have a Recordset object  which contains data from a table "XYZ". 

Currently i use this object to extract data using  %Get(COL1,COL2...) in a loop and than pass it to a function which inserts the data into another dynamically created  Table "ABC"  for each record. This takes a lot of time when 100's of records.

Is there a way i can directly copy a RecordSet to a dynamic table without looping through..?

Something like copy Recordset (COL1,COL2..)--> "ABC"

Thanks,

Jimmy

Answers

There is another way directly with SQL 

First, you CREATE a temporary table according to your needs (or have it ready)

CREATE GLOBAL TEMPORARY TABLE MyTemp.Table temp1, temp2, . . . . .

 

Next, you fill it by INSERT directly from SELECT

INSERT INTO  MyTemp.Table  (temp1,Temp2, . . .) 
      SELECT COL1,COL2, ...   FROM Source.Table WHERE ...... 

 

The select is the same as before.

Thank you Robert. But looks like i am running into another issue where data is lost and i am unable to create table.

I really would prefer to find out if there is a way to copy directly a RecordSet to a table or any other faster method rather than looping through the entire Recordset.

You shouldn't be so much concerned on looping the RecordSet
INSERT ...... SELECT ....  
looks smaller and smarter  but does basically the same

Yep. Select INTO works great if the source and target tables are in same NAMESPACE. I need to copy the data across namespaces, for which i  have already some another method. 

But i found that copying recordset would also work, since once the object is created i can easily write to another table on a different "NAMESPACE". Only issue is to write to a target table from a recordset i need to loop it and am not aware of a straightforward copy.

some dirty trick to copy across namespace boundaries:

have a target class config but change the global references in STORAGE section from

^Package.ClassD and ^Package.ClassI to 
^|"namespace"|Package.ClassD and ^|"namespace"|Package.ClassI

Might be a different way to work across namespaces