Example 6 (Dynamic SQL Extract with Individual Inserts)
This example extracts data from a SELECT statement and uses an INSERT statement with the data. It extract the values and insert the values one by one.
PROCEDURE dynamic_sql_example ()
BEGIN
DECLARE sqltext VARCHAR DEFAULT
'INSERT INTO /shared/updates(c_varchar) VALUES(''';
DECLARE temp VARCHAR;
FOR x AS SELECT Categories.CategoryName
FROM /shared/access/Categories Categories
DO
SET temp = CAST(sqltext || x.categoryName ||''')' AS VARCHAR);
EXECUTE IMMEDIATE temp;
END FOR;
END