如何使用交叉连接将100万条记录插入到表数据库Oracle中

i want to insert one million record in oracle DB table. i have achieved similar task in mysql using cross join as follows :

我想在oracle DB表中插入100万条记录。我在mysql中使用cross join完成了类似的任务:

1) insert 10 records first.

1)先插入10条记录。

insert into spltest_sampleapl2 values (10001, 'aaaa');
insert into spltest_sampleapl2 values (10002, 'bbbbb');
insert into spltest_sampleapl2 values (10003, 'ccccc');
insert into spltest_sampleapl2 values (10004, 'dddddd');
insert into spltest_sampleapl2 values (10005, 'eeeeeeeee');
insert into spltest_sampleapl2 values (10006, 'ffffff');
insert into spltest_sampleapl2 values (10007, 'gggggggg');
insert into spltest_sampleapl2 values (10008, 'hhhhhh');
insert into spltest_sampleapl2 values (10009, 'iiiiii');
insert into spltest_sampleapl2 values (10010, 'jjjjjj');
commit;

2) using user-variable

2)使用用户变量

set @num := 10010;

3) insert records with single-join

3)用单连接插入记录

insert into apl2 (id, data) select (@num := @num + 1) ,s1.data from apl2 s1, apl2 s2, apl2 s3, apl2 s4,apl2 s5, apl2 s6;
commit;

Now i want to do the same on similar schema in Oracle. How to do it ?

现在我想对Oracle中的类似模式做同样的处理。怎么做呢?

3 个解决方案

#1

Create a table with 10 records, numbered 0 to 10:

创建一个有10条记录的表，编号为0到10:

INSERT INTO t (n) VALUES (0);
INSERT INTO t (n) VALUES (1);
...
INSERT INTO t (n) VALUES (9);

Now select a cross-join, utilizing as many aliases as you want 10^n count:

现在选择一个交叉连接,利用你想要尽可能多的别名10 ^ n数:

For 100 records:

100条记录:

INSERT INTO X 
SELECT t2.n*10 + t1.n FROM t t1, t t2

For 1000 records:

1000条记录:

INSERT INTO X 
SELECT t3.n*100 + t2.n*10 + t1.n FROM t t1, t t2, t t3

For 1,000,000 records:

1000000条记录:

INSERT INTO X 
SELECT t6.n*100000 + t5.n * 10000 + t4.n*1000 + t3.n*100 + t2.n*10 + t1.n FROM t t1, t t2, t t3

I'm pretty sure this is vanilla SQL that would work on any platform...

我很确定这是适用于任何平台的普通SQL……

#2

Set up a sequence and use that for the auto-numbering:

设置一个序列并使用它进行自动编号:

create sequence seq_apl2;

insert into apl2(id, data)
    select seq_apl2.nextval, s1.data
    from apl2 s1 cross join apl2 s2 cross join
         apl2 s3 cross join apl2 s4 cross join
         apl2 s5 cross join apl2 s6;

EDIT:

编辑:

If you don't have the ability to use sequences, then use row_number():

如果您没有使用序列的能力，那么使用row_number():

insert into apl2(id, data)
    select row_number() over (order by NULL), s1.data
    from apl2 s1 cross join apl2 s2 cross join
         apl2 s3 cross join apl2 s4 cross join
         apl2 s5 cross join apl2 s6;

You can add an offset if you like.

如果您愿意，可以添加一个偏移量。

#3

First i inserted record having id from 1000000 to 1000010 (10 records), then i used following command.

首先插入id从1000000到1000010(10条记录)的记录，然后使用以下命令。

insert into apl2(id, data) select rownum ,s1.data from apl2 s1, apl2 s2, apl2 s3, apl2 s4, apl2 s5, apl2 s6 where rownum < 1000001;

#1