INSERT SAME RECORD MULTIPLE TIMES IN THE SAME TABLE USING SQL QUERY

November 22, 2021

I need to write the SQL query to duplicate 25 records in the same table after incrementing two of its fields at every insert. I have tried to use INSERT FROM SELECT but I can not update the fields using this and the identical record is inserted using this.

Thanks in Advance.

>Solution :

As of Oracle, selecting from dual along with the connect by clause might be one option. Here’s an example:

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

Table created.

SQL> insert into test
  2  select 0 + level, 'Little', 'Clerk'
  3  from dual
  4  connect by level <= 5;

5 rows created.

SQL> select * from test;

        ID NAME       JOB
---------- ---------- ----------
         1 Little     Clerk
         2 Little     Clerk
         3 Little     Clerk
         4 Little     Clerk
         5 Little     Clerk

SQL>