根据查询结果动态生成sql插入语句 [英] dynamically generate sql insert statements from results of a query
问题描述
我必须根据db2和oracle数据库的查询结果生成一系列插入语句.实际上是通过这样的查询
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);
我是sql的新手,如何生成此插入序列?
I'm a newbie in sql, how can i generate this sequence of inserts ?
感谢您的帮助
推荐答案
非常简单.只需使用您现有的查询,然后修改您的SELECT
以将INSERT
语句与g.id
值连接起来即可.
It's pretty easy. Just take your existing query, and modify your SELECT
to concatenate the INSERT
statement with the g.id
value.
仅供参考:||
是Oracle的字符串连接运算符.我对DB2不熟悉,但是我相信它支持用于连接字符串的相同语法.因此查询应该对两个数据库都有效(希望如此).
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%';
这篇关于根据查询结果动态生成sql插入语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!