How To Insert Multiple Rows with One INSERT Statement

Q

How To Insert Multiple Rows with One INSERT Statement? - Oracle DBA FAQ - Understanding SQL DML Statements

✍: FYIcenter.com

A

If you want to insert multiple rows with a single INSERT statement, you can use a subquery instead of the VALUES clause. Rows returned from the subquery will be inserted the target table.

The following tutorial exercise gives a good example:

INSERT INTO fyi_links
  SELECT department_id, department_name||'.com', NULL, NULL,
    SYSDATE FROM departments WHERE department_id >= 250;
3 row created.

SELECT * FROM fyi_links;
   ID URL                      NOTES     COUNTS CREATED
----- ------------------------ -------- ------- ---------
  101 http://dev.fyicenter.com NULL           0 30-Apr-06
  102 http://dba.fyicenter.com NULL           0 07-MAY-06
  103 http://sqa.fyicenter.com NULL     NULL    07-MAY-06  
  250 Retail Sales.com         NULL     NULL    07-MAY-06
  260 Recruiting.com           NULL     NULL    07-MAY-06
  270 Payroll.com              NULL     NULL    07-MAY-06

2007-04-21, 5287👍, 0💬