Insert into private temp table with multiple select statements

2
votes

I have a private temp table and I want to insert records into the table from multiple select statements from different tables.

CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (
  ID NUMBER,
  name VARCHAR(20),
  docType VARCHAR(20)
)

INSERT INTO ora$ptt_my_temp_table(ID, name, docType)
(SELECT ID FROM Case WHERE userCase = 'test'),
(SELECT userID FROM CASE where ID = '3'),
(SELECT title from Version WhERE ID = '3')

I thought it would have been something like that but I get missing expression or sometimes too many values. I am not sure what is wrong. Sorry not used to Oracle SQL, have been working with SQL Server for a while

1
sqloracle

1 Answers

1
votes

You can use select . . . from dual:

INSERT INTO ora$ptt_my_temp_table (ID, name, docType)
    SELECT (SELECT ID FROM Case WHERE userCase = 'test'),
           (SELECT userID FROM CASE where ID = '3'),
           (SELECT title from Version WhERE ID = '3')
    FROM dual;