Insert same data multiple times

Question

I have an insert statement similar to this:

insert into table (id, name, descr) values (4, 'asdf', 'this is not a word');

I need to insert this same statement with multiple ids. Right now I have:

insert into table (id, name, descr) values (4, 'asdf', 'this is not a word');
insert into table (id, name, descr) values (6, 'asdf', 'this is not a word');
insert into table (id, name, descr) values (7, 'asdf', 'this is not a word');
insert into table (id, name, descr) values (9, 'asdf', 'this is not a word');

Am I just going to have to run this, or is there a more condensed version?

score 9 · Accepted Answer · answered Oct 21 '14 at 00:39

Use a select . . . insert:

insert into table(id, name, descr) 
    select i.id, 'asdf', 'this is not a word'
    from (select 4 as id from dual union all
          select 6 from dual union all
          select 7 from dual union all
          select 9 from dual
         ) i;

score 2 · Answer 2 · answered Oct 21 '14 at 06:41

You can use the INSERT ALL statement

INSERT ALL
  INTO table (id, name, descr) VALUES (4, 'asdf', 'this is not a word')
  INTO table (id, name, descr) VALUES (6, 'asdf', 'this is not a word')
  INTO table (id, name, descr) VALUES (7, 'asdf', 'this is not a word')
  INTO table (id, name, descr) VALUES (9, 'asdf', 'this is not a word')
SELECT * FROM dual;

score 2 · Answer 3 · answered Oct 21 '14 at 10:16

2

INSERT INTO [TableName] (id, name, descr) VALUES 
(4, 'asdf', 'this is not a word'),
(6, 'asdf', 'this is not a word'),
(7, 'asdf', 'this is not a word'),
(9, 'asdf', 'this is not a word')

answered Oct 21 '14 at 10:16

ccStars

817
2
11
34

Gary_W · Answer 4 · 2014-10-23T18:42:20.733

For the sake of argument, one could create a more permanent solution if that ID is also the primary_key by creating a sequence, adding a BEFORE INSERT trigger to the table to increment the ID using the sequence automatically, then loop, inserting however many rows you want and let the ID increment itself:

-- Create the table    
CREATE TABLE SEQ_TEST
(
  ST_ID    NUMBER,
  ST_NAME  VARCHAR2(50 BYTE),
  ST_DESC  CHAR(100 BYTE)
);

-- Create the sequence
CREATE SEQUENCE SEQ_TEST_SEQ
  START WITH 1
  MAXVALUE 9999999999999999999999999999
  MINVALUE 0
  NOCYCLE
  NOCACHE
  ORDER;

-- Create the before insert trigger
CREATE OR REPLACE TRIGGER SEQ_TEST_BI
BEFORE INSERT
ON SEQ_TEST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN

  if :old.ST_ID is null then
    :new.ST_ID := SEQ_TEST_SEQ.nextval;
  end if;

END SEQ_TEST_BI;

-- insert 25 rows using an anonymous block.  Note the ID is NULL 
--  which causes the trigger to increment ID
--  based on the sequence.
begin
  for i in 1..25
  loop
    -- NOTE - Technically you could omit the 'ST_ID' and NULL and it would
    --        still work, but I prefer to keep them here to show this action 
    --        of inserting NULL is intentional and show that all columns are
    --        accounted for in the insert.  
    insert into SEQ_TEST (ST_ID, ST_NAME, ST_DESC) values (NULL, 'asdf', 'this is not a word');
  end loop;
end;

commit;

-- Prove it.
select * from seq_test;

Insert same data multiple times

4 Answers4

Linked