Insert data from another table multiple times?-CodePudding

I want to insert data from Table_A into Table_B . Rules : Insert 2 records from Table_A into Table_B ,if the records already exits in Table_B,skip it and insert the next one.

eg:

Table_A

ID(Key)  Name
1      John
2      Noah
3      Mary
4      Roland
5      Ivan

Table B
ID  Name OtherColumns
1   John   xxxxxxxxx
4   Roland xxxxxxxxx

Result:

Table B
ID  Name OtherColumns
1   John   xxxxxxxxx
2   Noah   xxxxxxxxx
2   Noah   xxxxxxxxx
2   Noah   xxxxxxxxx
3   Mary   xxxxxxxxx
3   Mary   xxxxxxxxx
3   Mary   xxxxxxxxx
4   Roland xxxxxxxxx

This is my SQL query but it not works well

Declare 
i:= 0;

Begin
For i in 1..3 loop

insert into Table_B
(
ID,Name,OtherColumns
)
select 
(
ID,Name,'xxxxxxxxx'
)
from 
Table_A
where ID not in 
(
select ID from Table_B
)
and rownum < 3;
end loop ;
end ;

Anyone can tell me what is the problem ...? >_<

CodePudding user response：

Rules: Insert 2 [copies of each] record from Table_A into Table_B, if the records already exits in Table_B, skip it and insert the next one.

Use a MERGE statement and a row generator to duplicate the rows:

MERGE INTO table_b dst
USING (
  SELECT a.*
  FROM   table_a a
         CROSS JOIN (
           SELECT LEVEL AS id FROM DUAL CONNECT BY LEVEL <= 2
         ) l
) src
ON (src.id = dst.id)
WHEN NOT MATCHED THEN
  INSERT (id, name, othercolumns)
  VALUES (src.id, src.name, 'xxxxxxxxx');

Which, for your sample data:

CREATE TABLE Table_A ( ID, Name ) AS
SELECT 1, 'John'   FROM DUAL UNION ALL
SELECT 2, 'Noah'   FROM DUAL UNION ALL
SELECT 3, 'Mary'   FROM DUAL UNION ALL
SELECT 4, 'Roland' FROM DUAL UNION ALL
SELECT 5, 'Ivan'   FROM DUAL;

CREATE TABLE Table_B (ID, Name, OtherColumns) AS
SELECT 1, 'John',   'xxxxxxxxx' FROM DUAL UNION ALL
SELECT 4, 'Roland', 'xxxxxxxxx' FROM DUAL;

Then after the MERGE the table contains:

ID	NAME	OTHERCOLUMNS
1	John	xxxxxxxxx
4	Roland	xxxxxxxxx
2	Noah	xxxxxxxxx
2	Noah	xxxxxxxxx
3	Mary	xxxxxxxxx
3	Mary	xxxxxxxxx
5	Ivan	xxxxxxxxx
5	Ivan	xxxxxxxxx

fiddle