Clever Geek Handbook

Insert the same data multiple times

I have an insert operator like 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');

Do I just need to run this, or is there a more concise version?

+3


source to share


4 answers


Use 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;
+7


source


You can use the operator INSERT ALL 



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;
+2


source 


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')
+1


source


For argumentation, a more permanent solution can be made if that id is also main_record by creating a sequence, adding a BEFORE INSERT trigger to the table to automatically increment the id using the sequence, then looping, inserting however many rows you want, and let yourself increment of identifier:

-- 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;
0


source






More articles:


All Articles