oracle 多次插入相同的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26477120/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert same data multiple times
提问by O P
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:
我需要使用多个 id 插入相同的语句。现在我有：
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?
我只需要运行这个，还是有更精简的版本？
回答by Gordon Linoff
Use a select . . . insert:
使用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;
回答by Wernfried Domscheit
You can use the INSERT ALLstatement
您可以使用该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;
回答by ccStars
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')
回答by Gary_W
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:
为了论证，如果该 ID 也是 primary_key，则可以创建一个更持久的解决方案，方法是创建一个序列，在表中添加一个 BEFORE INSERT 触发器以使用该序列自动增加 ID，然后循环，插入多少行想要并让 ID 自行增加：
-- 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;
