CODEWITHSUNDEEP
sqloracle-database
dang
dang

Reputation: 2412

Replicate data with new ID - Oracle SQL

I have a table:

u_a_id   c_id    c_val
1        100     abc
1        101     xyz
1        102     www

2        100     qqq
2        101     rrr
2        102     ttt

I have 100 such u_a_id. I need to replicate the same content but by increasing u_a_id by 100, 10 times, so the total records become 1000.

So, the output would be:

u_a_id   c_id    c_val
1        100     abc
1        101     xyz
1        102     www

2        100     qqq
2        101     rrr
2        102     ttt

101      100     abc
101      101     xyz
101      102     www

102      100     qqq
102      101     rrr
102      102     ttt

Is there a quick way of doing it?

Upvotes: 0

Views: 44

Answers (4)

MT0
MT0

Reputation: 168416

You can use a recursive sub-query factoring clause:

Oracle Setup:

CREATE TABLE table_name ( u_a_id, c_id, c_val ) AS
  SELECT 1, 100, 'abc' FROM DUAL UNION ALL
  SELECT 1, 101, 'xyz' FROM DUAL UNION ALL
  SELECT 1, 102, 'www' FROM DUAL UNION ALL
  SELECT 2, 100, 'qqq' FROM DUAL UNION ALL
  SELECT 2, 101, 'rrr' FROM DUAL UNION ALL
  SELECT 2, 102, 'ttt' FROM DUAL

Insert:

INSERT INTO table_name ( u_a_id, c_id, c_val )
  WITH rsqfc ( u_a_id, c_id, c_val ) AS (
    SELECT u_a_id + 100, c_id, c_val
    FROM   table_name
  UNION ALL
    SELECT u_a_id + 100, c_id, c_val
    FROM   rsqfc
    WHERE  u_a_id < 1000
  )
  SELECT u_a_id, c_id, c_val
  FROM   rsqfc

Output:

SELECT * FROM table_name

U_A_ID | C_ID | C_VAL
-----: | ---: | :----
     1 |  100 | abc  
     1 |  101 | xyz  
     1 |  102 | www  
     2 |  100 | qqq  
     2 |  101 | rrr  
     2 |  102 | ttt  
   101 |  100 | abc  
   101 |  101 | xyz  
   101 |  102 | www  
   102 |  100 | qqq  
   102 |  101 | rrr  
   102 |  102 | ttt  
   201 |  100 | abc  
   201 |  101 | xyz  
   201 |  102 | www  
   202 |  100 | qqq  
   202 |  101 | rrr  
   202 |  102 | ttt  
...
  1001 |  100 | abc  
  1001 |  101 | xyz  
  1001 |  102 | www  
  1002 |  100 | qqq  
  1002 |  101 | rrr  
  1002 |  102 | ttt

db<>fiddle here

Upvotes: 2

Usagi Miyamoto
Usagi Miyamoto

Reputation: 6329

Something like this:

INSERT INTO table
  SELECT u_a_id +100 AS u_a_id, c_id, c_val
    FROM table
    WHERE u_a_id BETWEEN 1 AND 100

After running it, re-run with +200, +300, and so on instead of +100...

Upvotes: 1

APC
APC

Reputation: 146309

I presume you want your data sets to have u_a_id = 1 expand to 101, 201, up to 901.

Use a WITH clause to generate 9 numbers, which are multiplied by 100. Cross join this with the original table to get the increased set:

insert into your_table
with cte as ( 
      select level * 100 as val
      from dual
      connect by level <= 9 )
select t.u_a_id + cte.val
       , t.c_id
       , t.c_val
from your_table t
     cross join cte

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270713

In a select you would do:

select u_a_id, c_id, c_val
from t
union all
select u_a_id + 100, c_id, c_val
from t
where u_a_id in ( . . . )   -- your list here

Or to insert the rows in the table:

insert into t (u_a_id, c_id, c_val)
    select u_a_id + 100, c_id, c_val
    from t
    where u_a_id in ( . . . );   -- your list here

Upvotes: 1

Related Questions