0
979
Question Jimmy Christian · May 31, 2019

Cache Sql Record set copy to a table

#Caché #SQL

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

Discussion (6)0
Log in or sign up to continue

Comments

Robert Cemper · May 31, 2019

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.

0
Jimmy Christian  May 31, 2019 to Robert Cemper

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.

0
Robert Cemper  May 31, 2019 to Jimmy Christian

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

0
Jimmy Christian  Jun 3, 2019 to Robert Cemper

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.

0
Robert Cemper  Jun 3, 2019 to Jimmy Christian

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

0