Procedure for adding one column from one table to another table and its data

0
votes

I have two tables table1 and table2.

Table 1 has three columns 

id  name   age
----------------
 1  ram    27
 2  rafi   30

Table 2- 

no  place 
--------------
101  agra
102  delhi
103  chennai
104  hyd

In this situation I want to create a procedure to get the no column of table2 will be added to id column of table1 and the remaining data should be copied same and and if the count of table2 is more then the data should be repeated as shown below 

id    name    age
-----------------
  1   ram     27
  2   rafi    30
101   ram     27
102   rafi    30
103   ram     27
104   rafi    30

Please help

2
sqloraclestored-proceduresplsql
Why is there 101 ram 27 and not 101 rafi 30? How do you know which value to take from table1? - Pavel Smirnov
i need that repetition.so irequested the same.after last data it should go to first and this should be continued until the all records n table2 will be filled - M R S REDDY

2 Answers

0
votes

Assuming table1.id1 is a monotonically increasing series starting at 1 this simple trick will work:

insert into table1
select sq2.no#
       , t1.name
       , t1.age
from table1 t1
  inner join (       
    select t2.no#
           , mod(rownum, sq.cnt)+1 as mod#
    from table2 t2
    cross join (select count(*) as cnt from table1) sq 
  ) sq2 on sq2.mod# = t1.id
/

There is a demo on db<>fiddle.

If table1.id1 contains gaps, or does not start from 1, you will need to replace table1 in the FROM clause above with another subquery ...

(select t1.*
        , row_number() over (order by t1.id) as mod#
 from table1 t1 ) sq1

... and inner join that to the existing subquery on mod#.

-1
votes

You can achieve it using row_number analytical function with join using MOD function as following:

SQL> WITH TABLE_1(ID, NAME, AGE)
  2  AS (SELECT 1, 'ram', 27 FROM DUAL UNION ALL
  3      SELECT 2, 'rafi', 30 FROM DUAL),
  4  TABLE_2(NO, PLACE)
  5  AS (SELECT 101, 'agra' FROM DUAL UNION ALL
  6      SELECT 102, 'delhi' FROM DUAL UNION ALL
  7      SELECT 103, 'chennai' FROM DUAL UNION ALL
  8      SELECT 104, 'hyd' FROM DUAL)
  9  -- YOUR QUERY STARTS FROM HERE
 10  SELECT ID, NAME, AGE FROM TABLE_1
 11  UNION ALL
 12  SELECT T2.NO, T1.NAME, T1.AGE
 13  FROM
 14      (SELECT T.*,
 15          ROW_NUMBER() OVER( ORDER BY ID DESC) AS R,
 16          COUNT(1) OVER() C
 17      FROM TABLE_1 T ) T1
 18      --
 19      JOIN (SELECT T.*,
 20              ROW_NUMBER() OVER( ORDER BY NO ) AS R,
 21              COUNT(1) OVER() C
 22          FROM TABLE_2 T ) T2
 23      ON ( MOD(T2.R, T1.C) = T1.R - 1 )
 24  ORDER BY ID;

        ID NAME        AGE
---------- ---- ----------
         1 ram          27
         2 rafi         30
       101 ram          27
       102 rafi         30
       103 ram          27
       104 rafi         30

6 rows selected.

SQL>

Cheers!!