I have learned the hard way that ExecuteSQL supports SQL SELECT but not INSERT. Therefore I am exploring the alternative methods and hope you may be able to give me some ideas, please.
The user enters some records into a portal, let’s say five records. I can now write an SQL SELECT UNION and GROUP statement to take those five records and create more records (anything from one record (minimum) and up to five [EDIT ten] records in this case). I now want to INSERT these records into another table, except I cannot via ExecuteSQL.
Leaving aside the possibility of using any plugins or linking to other ODBC connections is there a way I could use the “contents” of the SQL Select statement to populate a New Record/Request script step?
An option I can think of, that might be a little long winded and does not use the SQL Select statement, but nevertheless is to:
- From source Portal loop through records – switching to/from destination table/layout and adding records
- Repeat a) for each of the required SQL UNION statements but this time use variables to filter on the GROUPED fields. Then step through all records using the “Constrain Found Set” step to prevent repeating use of any record, until found set is empty.
Any thoughts or suggestions most welcomed and thank you.