How to copy all data in a table into the same table, but only changing one column to have subsequent numbers

Question

I have to do the following and can't figure out how to do it all correctly:

I have a table test

Test1	Test2
1	ABC
2	DEF

I want to duplicate this, and have the test1 column have subsequent numbering. When I do a simple insert I can of course just duplicate it all and I have what I need. But I can't get the numbering of Test1 right.

The result I'm looking for is this:

Test1	Test2
1	ABC
2	DEF
3	ABC
4	DEF

What I'm getting at the moment is:

Test1	Test2
1	ABC
2	DEF
1	ABC
2	DEF

I tried the following but did not get subsequent numbering in the test1 column:

INSERT INTO test (test1,
test2) 

SELECT test,
test

FROM test; drop table if exists temp.tmp; 
create temporary table tmp as
select test1, row_number() over (order by test1) rn
from test; update test
set test1 = (
  select rn from temp.tmp
  where temp.tmp.test1 = test.test1
  );
  drop table temp.tmp;

Answer 1

You can use a window function for this task. Specifically, you can duplicate all your rows, yet increasing the Test1 values, by adding the current total count of records to every record of your table.

INSERT INTO tab
SELECT Test1 + COUNT(*) OVER(), Test2 FROM tab;

Output:

TEST1	TEST2
1	ABC
2	DEF
3	ABC
4	DEF

Check the demo here.

Answer 2

How about this?

Sample data:

SQL> select * from test;

     TEST1 TES
---------- ---
         1 ABC
         2 DEF
         5 GHI

Insert duplicates into test2 column:

SQL> insert into test (test2) select test2 from test;

3 rows created.

Fix numbering:

SQL> update test set test1 = rownum;

6 rows updated.

Result:

SQL> select * from test;

     TEST1 TES
---------- ---
         1 ABC
         2 DEF
         3 GHI
         4 ABC
         5 DEF
         6 GHI

6 rows selected.

SQL>

Answer 3

Creating an identical table which contains an IDENTITY column would be straightforward I think, provided that the DB's version is 12c+, such as

CREATE TABLE Test_( Test1 INT GENERATED ALWAYS AS IDENTITY, Test2 VARCHAR2(99) );

INSERT INTO Test_(Test2) SELECT Test2 FROM Test ORDER BY Test1; -- run as many times as you wish

DROP TABLE Test;

ALTER TABLE Test_ RENAME TO Test;

SELECT * FROM Test; -- assuming the INSERT Statement above run twice

TEST1 | TEST2
------|-------
1     |  ABC
2     |  DEF
3     |  ABC
4     |  DEF

Demo