How to create insert statements script from two tables

Question

I'm using oracle SQL and I need to insert data to multiple users but I need to create insert statements first and replace only user id from the first insert statement as below :

1- This is the insert statement :

    INSERT INTO SYS_USER_FUNCTIONS ( USER_ID, FUNCTION_ID_PARENT, FUNCTION_ID_CHILD, A, U,
    D, V, R, CREATING_USER, CREATING_DATE, CREATING_TIME, LAST_UPDATE_DATE, LAST_UPDATE_COUNT,
    LAST_UPDATE_USER, LAST_UPDATE_TRANSACTION, S ) 
   VALUES ( 'JAD', 'DOCMDOC', 'OPDA100', 1, 1, 2, 1, 2, 'ZYAD', 20210112, 1515, 20210112
    , 1, 'ZYAD', 'I', 2); 

COMMIT;

2- I need to generate insert statements and replace only `

USER_ID

and select USER_ID from the following table

SELECT USER_ID 
FROM USERS 
WHERE USER_FUNCTION = 'DOCMDOC'

How can I do that ?

Gordon Linoff · Accepted Answer · 2021-01-12 17:10:29Z

2

I think you just want insert . . . select:

INSERT INTO SYS_USER_FUNCTIONS ( USER_ID, FUNCTION_ID_PARENT, FUNCTION_ID_CHILD, A, U,
    D, V, R, CREATING_USER, CREATING_DATE, CREATING_TIME, LAST_UPDATE_DATE, LAST_UPDATE_COUNT,
    LAST_UPDATE_USER, LAST_UPDATE_TRANSACTION, S ) 
   SELECT u.id, 'DOCMDOC', 'OPDA100', 1, 1, 2, 1, 2, 'ZYAD', 20210112, 1515, 20210112
    , 1, 'ZYAD', 'I', 2
   FROM Users
   WHERE USER_FUNCTION = 'DOCMDOC';

answered Jan 12, 2021 at 17:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wspeedy · Accepted Answer · 2021-01-12 17:37:21Z

You can use simple for-loop plsql block for multiple users and modify what you want:

begin
for i in (SELECT USER_ID FROM USERS WHERE USER_FUNCTION = 'DOCMDOC')
loop
INSERT INTO SYS_USER_FUNCTIONS ( USER_ID, FUNCTION_ID_PARENT, FUNCTION_ID_CHILD, A, U,
    D, V, R, CREATING_USER, CREATING_DATE, CREATING_TIME, LAST_UPDATE_DATE, LAST_UPDATE_COUNT,
    LAST_UPDATE_USER, LAST_UPDATE_TRANSACTION, S ) 
   VALUES ( i.USER_ID, 'DOCMDOC', 'OPDA100', 1, 1, 2, 1, 2, 'ZYAD', 20210112, 1515, 20210112
    , 1, 'ZYAD', 'I', 2); 
end loop;
end;

Or insert...select for this case only.

INSERT INTO SYS_USER_FUNCTIONS ( USER_ID, FUNCTION_ID_PARENT, FUNCTION_ID_CHILD, A, U, D, V, R, CREATING_USER, CREATING_DATE, CREATING_TIME, LAST_UPDATE_DATE, LAST_UPDATE_COUNT,LAST_UPDATE_USER, LAST_UPDATE_TRANSACTION, S ) 
SELECT USER_ID, 'DOCMDOC', 'OPDA100', 1, 1, 2, 1, 2, 'ZYAD', 20210112, 1515, 20210112, 1, 'ZYAD', 'I', 2 FROM USERS WHERE USER_FUNCTION = 'DOCMDOC';

Collectives™ on Stack Overflow

How to create insert statements script from two tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related