dynamically generate sql insert statements from results of a query

Question

I have to generate a series of insert statements based on the results of a query for a db2 and oracle databases. In practice from a query like this

select g.id from SUV_OWNER.gruppi g 
WHERE EXISTS (SELECT 1 FROM SUV_OWNER.GRUPPIRUOLIPROCEDURE grp WHERE grp.gruppoid=g.gruppoid 
AND GRP.RUOLOID = 50)
AND 
G.CHIAVE LIKE 'ANA%';

which generates as output a sequence of numbers not known in advance as

30000  
30001  
.....

I have to generate for each number an insert statement as

insert into SUV_OWNER.GRUPPIRUOLIPROCEDURE (GRUPPOID, RUOLOID, PROCEDURAID) values  (30000, 141, 7);  
insert into SUV_OWNER.GRUPPIRUOLIPROCEDURE (GRUPPOID, RUOLOID, PROCEDURAID) values  (30001, 141, 7);

I'm a newbie in sql, how can i generate this sequence of inserts ?

Thanks for your help

Would you mind a more efficient approach of the form, INSERT INTO suv_owner.gruppiruoliprocedure ( ... ) (SELECT .....FROM suv_owner.gruppi g .... ) — Patrick Bacon
– Patrick Bacon, Commented Jul 17, 2015 at 19:26

sstan · Accepted Answer · 2015-07-17 19:12:08Z

2

It's pretty easy. Just take your existing query, and modify your SELECT to concatenate the INSERT statement with the g.id value.

FYI: || is Oracle's string concatenation operator. I'm not familiar with DB2, but I believe it supports the same syntax for concatenating strings. So the query should work for both databases (I hope).

select 'insert into SUV_OWNER.GRUPPIRUOLIPROCEDURE (GRUPPOID, RUOLOID, PROCEDURAID) values (' || g.id || ', 141, 7);'
from SUV_OWNER.gruppi g 
WHERE EXISTS (
  SELECT 1 
  FROM SUV_OWNER.GRUPPIRUOLIPROCEDURE grp 
  WHERE grp.gruppoid=g.gruppoid 
  AND GRP.RUOLOID = 50)
AND G.CHIAVE LIKE 'ANA%';

edited Jul 17, 2015 at 19:12

answered Jul 17, 2015 at 18:59

sstan

36.8k7 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

dynamically generate sql insert statements from results of a query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related