把select的结果插入数据库

Question

with a(transdate, summarycode, pubaccnum,hostsernum, transcode) as (
select a.transdate, trim(a.summarycode), a.pubaccnum, a.hostsernum, a.transcode from pb600 a where a.transdate >= '2021-05-01' and a.transdate <= '2021-05-31' and a.summarycode in ('8156','8116','8115','8150','8114','8110','8151','8109','8105','8120','8104','8143','8142','8003','8129','8138','8139','8014','8015','8017','8117','8016','8141','8009','8010','8132','8133','1534','8147','8123','2198','8157','8013')
)
select a.summarycode, (select x.summarydes from pb002 x where x.summarycode = a.summarycode) as summary,count(1) from a where a.summarycode in ('8156','8116','8115','8150','8114','1534','8147','8157','8139','8138','8143','8142','8013','8129','8138','8014','8015','8017','8117','8016','8141','8009','8010','8132','8133') group by a.summarycode

上面的 这个没问题 可以执行，但是想把执行的结果插入表里面就不行了

报错104 遇到非法符号 这个要怎么写呢？

Answer 1

你既然是insert报错,那么就应该把insert命令发出来呀。
使用with的时候,insert 要放到with前面去

create table test_20220123_01(a number);

insert into test_20220123_01(a)
with xx as (select 1 a from dual)
select a from xx;

---

你都能用sql查出数据了,为什么不直接用查询sql去插,还导出成值再插？
还有,你发的这个sql也不完整啊,指定了7个字段,那么后面的值也有接7个,并且按顺序一一对应。
你这后面括号里只有4个值,而且还是以逗号结尾,当然会报错。
正常的单行插入sql应该长这样

insert into 表名(字段1, 字段2, 字段7, 字段8 ) VALUES ( '字段1的值','字段2的值','字段7的值','字段8的值')