CODEWITHSUNDEEP
sqloracle-databasesequence
strudel
strudel

Reputation: 61

SQL: How to write multiple interactive scripts in one query (insert rows)

Simple question- I just want to learn how to write a query that will insert 2 rows into my table by writing one query, instead of 2 separate ones. My queries work completely fine, I just want to know a simpler way, if that's possible. Below are my 2 queries that I'd like to combine into 1.

INSERT INTO dept (deptno, dname)
VALUES (dept_id_seq.NEXTVAL, 'EDUCATION');

INSERT INTO dept (deptno, dname)
VALUES (dept_id_seq.NEXTVAL, 'ADMINISTRATION');

As I stated above, my code works as expected. Here is my output with NO errors-

DE DNAME                          LOC
-- ------------------------------ ------------------------------
10 ACCOUNTING                     NEW YORK
20 RESEARCH                       DALLAS
30 SALES                          CHICAGO
40 OPERATIONS                     BOSTON
60 EDUCATION
70 ADMINISTRATION

Thank you for anyone's help!

Upvotes: 2

Views: 262

Answers (4)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Or you can use a MERGE with only the NOT MATCHED branch filled in:

MERGE INTO DEPT d
  USING (SELECT 'EDUCATION' AS DNAME FROM DUAL UNION ALL
         SELECT 'ADMINISTRATION' AS DNAME FROM DUAL) s
    ON (d.DNAME = s.DNAME)
  WHEN NOT MATCHED THEN
    INSERT (DEPTNO, DNAME)
    VALUES (DEPT_ID_SEQ.NEXTVAL, s.DNAME)

Upvotes: 1

RandyMcKay
RandyMcKay

Reputation: 326

Not sure if this is what would you consider, but you can do it like this:

INSERT INTO dept

SELECT dept_id_seq.NEXTVAL, 'EDUCATION' UNION ALL

SELECT dept_id_seq.NEXTVAL, 'ADMINISTRATION'

Upvotes: 2

Thiago Esser
Thiago Esser

Reputation: 21

You can use the following syntax:

INSERT ALL
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;

Upvotes: 2

alexherm
alexherm

Reputation: 1362

Its not totally clear what you're asking for, but you can make your inserts 1 script execution with:

BEGIN
INSERT INTO dept (deptno, dname) VALUES (dept_id_seq.NEXTVAL, 'EDUCATION');
INSERT INTO dept (deptno, dname) VALUES (dept_id_seq.NEXTVAL, 'ADMINISTRATION');
END;

However it looks like you may want to use a cursor to loop through the list of values and insert them somewhere else.

Upvotes: 2

Related Questions