显然，您可以使用某个临时表来存储相同的结果集，但也可以使用相同的语句插入多个表。

CREATE TABLE A AS (SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES) DEFINITION ONLY;
CREATE TABLE B AS (SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES) DEFINITION ONLY;

WITH 
  T AS (SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES)
, IA AS 
(
  SELECT *
  FROM NEW TABLE 
  (
    INSERT INTO A (TABSCHEMA, TABNAME)
    SELECT TABSCHEMA, TABNAME
    FROM T
    WHERE TABSCHEMA LIKE 'SYS%'
  )
)
, IB AS 
(
  SELECT *
  FROM NEW TABLE 
  (
    INSERT INTO B (TABSCHEMA, TABNAME)
    SELECT TABSCHEMA, TABNAME
    FROM T
    WHERE TABSCHEMA NOT LIKE 'SYS%'
  )
)
-- You may use whatever SELECT statement here instead like:
-- SELECT 1 FROM SYSIBM.SYSDUMMY1
-- You can't use "select into nowhere" in SP, so you need something
-- like below with some V_DUMMY variable declared
-- SELECT 1 INTO V_DUMMY FROM SYSIBM.SYSDUMMY1
SELECT 
  (SELECT COUNT (1) CNT FROM IA) AS A_CNT
, (SELECT COUNT (1) CNT FROM IB) AS B_CNT
FROM SYSIBM.SYSDUMMY1

| A_计数器|B_计数器|
| - -|- -|
| 四百三十二|四十一|

SELECT COUNT (1) AS A_CNT FROM A

| A_计数器|
| - -|
| 四百三十二|

SELECT COUNT (1) AS B_CNT FROM B

| B_计数器|
| - -|
| 四十一|
dbfiddle link

应该看起来像这样：

INSERT INTO TABLE_A (COL_1, COL2)
WITH TEMP AS ( SELECT COL_1, COL_2
                 FROM TABLE_A
                 JOIN ...
                 WHERE ... )
SELECT COL_1, COL_2
 FROM TEMP;