I have to take a record from a table, increment one column and insert the record in the same table again.

This is my table for example:

TEST(id, name, city)
      1, john, NY

I want to select this record and insert it in the same table 1250 times, expected output is:

id, name, city
2, john, NY
3, john, NY
4, john, NY
5, john, NY
6, john, NY
7, john, NY

...

CodePudding user response：

Table:

SQL> create table test (id number, name varchar2(10), city varchar2(10));

Table created.

Its initial contents:

SQL> insert into test values (1, 'John', 'NY');

1 row created.

Code that adds some more rows:

SQL> insert into test
  2  select id   level, name, city
  3  from test
  4  connect by level <= 5;     --> would be 1250 in your case

5 rows created.

SQL> select * from test;

        ID NAME       CITY
---------- ---------- ----------
         1 John       NY
         2 John       NY
         3 John       NY
         4 John       NY
         5 John       NY
         6 John       NY

6 rows selected.

SQL>

CodePudding user response：

Create your table using an IDENTITY column (or, if you are using Oracle 11 or earlier, a SEQUENCE):

CREATE TABLE test(
  id   INTEGER
       GENERATED ALWAYS AS IDENTITY
       CONSTRAINT test_id__pk PRIMARY KEY,
  name VARCHAR2(20),
  city VARCHAR2(20)
)

Then insert your row:

INSERT INTO test (name, city) VALUES ('john', 'NY');

(Note: the ID column is auto-generated and you should not include it.)

Then you can clone your row:

INSERT INTO test (name, city)
SELECT name, city
FROM   test
WHERE  id = 1
CONNECT BY LEVEL <= 1249;

(Again, you do not need to do anything for the ID column as it will be auto-generated.)

Then:

SELECT * FROM test;

Outputs:

ID	NAME	CITY
1	john	NY
2	john	NY
3	john	NY
...	...	...
1249	john	NY
1250	john	NY

db<>fiddle here