How to insert the result of the select query in another table's column

user_14503621 0
Viewed 81
SELECT segment_name TABLE_NAME, SUM(bytes) table_size
  FROM USER_SEGMENTS
 WHERE SEGMENT_TYPE = 'TABLE'
 GROUP BY segment_name
 ORDER BY segment_name ASC;

INSERT INTO R_LOG (DOMAINID, SOURCENAME, TERMINALID,userid, DETAILTEXT)
VALUES (1, 'tables_job', 'TEST', 'GUEST',?????);

I want to store the query output (two columns) in a DETAILTEXT column (single column) of R_LOG table

SELECT segment_name TABLE_NAME, SUM(bytes) table_size
  FROM USER_SEGMENTS
 WHERE SEGMENT_TYPE = 'TABLE'
 GROUP BY segment_name
 ORDER BY segment_name ASC;

This part of my code is working totally fine - but I'm not able to insert the value.

Any help will be appreciated

oracle sql c#
3 Answers

Seems you need LISTAGG() function along with an INSERT statement

INSERT INTO R_LOG
SELECT 1, 'tables_job', 'TEST', 'GUEST', 
       LISTAGG('Table : '||segment_name||'- size: '|| SUM(bytes),', ') 
        WITHIN GROUP (ORDER BY segment_name)
  FROM USER_SEGMENTS
 WHERE SEGMENT_TYPE = 'TABLE'
 GROUP BY segment_name

If you get ORA-01489: result of string concatenation is too long error, then replace the last column containing LISTAGG() function with

RTRIM(XMLAGG(XMLELEMENT(e, 'Table : '||segment_name||'- size: '|| SUM(bytes), ',')
      .EXTRACT('//text()') ORDER BY segment_name)
      .GetClobVal(),
      ',')
user_58413060

I guess you want to concatenate the columns segment_name and SUM(bytes) in the destination column.
Use SELECT instead of VALUES:

INSERT INTO R_LOG (DOMAINID, SOURCENAME, TERMINALID,userid, DETAILTEXT) 
SELECT 1, 'tables_job', 'TEST', 'GUEST', segment_name || ' ' || SUM(bytes) 
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE='TABLE'
GROUP BY segment_name
ORDER BY segment_name ASC;
user_104988280

You can directly use the INSERT INTO <TABLE_NAME>[(columns)] SELECT .. as follows:

INSERT INTO R_LOG (
    DOMAINID,
    SOURCENAME,
    TERMINALID,
    USERID,
    DETAILTEXT
)
    SELECT 1, -- need to use some sequence here
           'tables_job',
           'TEST',
           'GUEST',
           SEGMENT_NAME || ' ' || SUM(BYTES)
      FROM USER_SEGMENTS
     WHERE SEGMENT_TYPE = 'TABLE'
     GROUP BY SEGMENT_NAME; 
     -- Order by is not needed
user_115656290
Related