Cache Sql Record set copy to a table

Question

Question

Jimmy Christian · May 31, 2019

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

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.

3 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

2 0

score 0 · Answer 1 · 2019-05-31T16:02:26-04:00

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.

score 2 · Answer 2 · 2019-05-31T17:25:48-04:00

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

score 0 · Answer 3 · 2019-06-03T09:34:18-04:00

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.

score 0 · Answer 4 · 2019-06-03T07:23:38-04:00

Hi @Jimmy Christian ,

Thanks to @Robert.Cemper for the "INSERT ...... SELECT .... " command.

You can find more help in the following link https://www.w3schools.com/sql/sql_insert_into_select.asp

Hope this helps.