Insert multiple rows with single a query from one table into another in Oracle

code_Finder

I have two tables, dcr_details_new and dcr_details_old where the primary key is DCRDID.

enter image description here

Both dcr_details_new and dcr_details_old tables share the same structure.The primary key is DCRDID where I increment by one in each insert.

I need to fetch all the rows from dcr_details_old into dcr_details_new where I have to filter the records by DOCREGNO and DCR_No

So a normal single row import/insert is running exactly as follows.

INSERT INTO dcr_details_new 
SELECT (select Max(DCRDID) + 1 from dcr_details_new),
       TWNCODE,
       '100008',
       DOCCATOGARY,
       DCR_NO,
       VISIT_NO,
       GIVEAWAY,
       COMPETITORBRN,
       REMARK,
       DCRDRDATE,
       COM_ACTI
 FROM dcr_details_old  
WHERE DOCREGNO= 'T10037'
  and DCR_NO = 28766;

1 rows created.

Now I want to skip filtering by dcr_no and insert the records as it contain too many records with different dcr_no's. Here when I filter only with DOCREGNO the select statements returns many records and when I try to loop the and insert I always mess up with the primary key as it is not incrementing as I wish.

Here is the loop I have tried and the error I'm getting.

declare
  i integer := 1;
BEGIN 
  FOR x IN (select * from dcr_details_old  WHERE DOCREGNO= 'T10037')
  LOOP
    INSERT INTO dcr_details_new (DCRDID, TWNCODE, DOCREGNO, DOCCATOGARY,
                                 DCR_NO, VISIT_NO, GIVEAWAY, COMPETITORBRN,
                                 REMARK, DCRDRDATE, COM_ACTI)
    SELECT (select Max(DCRDID) + 1 from dcr_details_new),
           TWNCODE,
           '100008',
           DOCCATOGARY,
           DCR_NO, VISIT_NO, GIVEAWAY, COMPETITORBRN,
           REMARK, DCRDRDATE, COM_ACTI
      FROM dcr_details_old;

     i := i + 1;
  END LOOP;
END;

Error:

**ERROR at line 1:
ORA-00001: unique constraint (SYS_C0061873) violated
ORA-06512: at line 5**

In this error SYS_C0061873 is the primary key DCRDID. Here in insert the value '100008' is the new docregno I have to put manually.

Any simple way to proceed? Kindly seek your assistance.

Marmite Bomber

To insert all records from OLD table to the new table with new primary keys use following statement.

Note that the key part is to get the maximal existing key and increates it using ROWNUM. For a signle migration step (i.e. no parallel migration and the application is down) this is a secure way.

 INSERT INTO dcr_details_new
 (DCRDID,TWNCODE,DOCREGNO,DOCCATOGARY,DCR_NO,VISIT_NO,GIVEAWAY,
 COMPETITORBRN,REMARK,DCRDRDATE,COM_ACTI)
 select 
  (select max(DCRDID) from dcr_details_new) + rownum as DCRDID,
  TWNCODE,DOCREGNO,DOCCATOGARY,DCR_NO,VISIT_NO,GIVEAWAY,
  COMPETITORBRN,REMARK,DCRDRDATE,COM_ACTI
 from dcr_details_old where DOCREGNO = 'T10037'
 ;

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

MySQL query - single row in one table with multiple rows in another table

From Dev

join single value from one table to multiple rows table - Oracle

From Dev

select from one table, insert into another table oracle sql query

From Dev

SQL insert multiple rows from single field in another table

From Dev

Sqlalchemy single query for multiple rows from one column in one table

From Dev

select multiple column from one table and insert into another as rows

From Dev

Select from multiple tables, insert into another table Oracle SQL query

From Dev

MYSQL Single query to retrieve both single row from one table and many rows as a single field from another

From Dev

Insert multiple rows from select into another table

From Dev

insert multiple rows mysql from another table

From Dev

Insert multiple rows from a single row inside an insert into select query

From Dev

How to insert multiple rows from a table to another table based on date condition (PHP-MySQL-Query)?

From Dev

looping mysql query to get multiple rows of data from a table and insert into another table

From Dev

Insert multiple rows in one table based on number in another table

From Dev

How to insert multiple rows into one table for each id of another table

From Dev

insert data from one table to a single column another table with no relation

From Dev

Insert many rows from a table into one unique row in another table

From Dev

Insert (multiple) new rows into a table from another table using a subquery?

From Java

How to insert multiple rows from a single query using eloquent/fluent

From Dev

Insert multiple values from one table to another using eloquent/query builder

From Dev

Insert Data from one table to another leaving the already existing rows

From Dev

Insert specific rows from one table in database into another with different columns

From Dev

Count number of rows from another table in single query

From Dev

INSERT rows multiple times based on a column value from another table

From Dev

oracle - How to insert unique values from a table to another multiple times?

From Dev

Insert multiple rows from two tables into one table

From Dev

Insert from another table, and update if key exists, possible in one query?

From Dev

Oracle Insert query from a table

From Dev

JQuery: How to drag multiple rows from one table to another?

Related Related

  1. 1

    MySQL query - single row in one table with multiple rows in another table

  2. 2

    join single value from one table to multiple rows table - Oracle

  3. 3

    select from one table, insert into another table oracle sql query

  4. 4

    SQL insert multiple rows from single field in another table

  5. 5

    Sqlalchemy single query for multiple rows from one column in one table

  6. 6

    select multiple column from one table and insert into another as rows

  7. 7

    Select from multiple tables, insert into another table Oracle SQL query

  8. 8

    MYSQL Single query to retrieve both single row from one table and many rows as a single field from another

  9. 9

    Insert multiple rows from select into another table

  10. 10

    insert multiple rows mysql from another table

  11. 11

    Insert multiple rows from a single row inside an insert into select query

  12. 12

    How to insert multiple rows from a table to another table based on date condition (PHP-MySQL-Query)?

  13. 13

    looping mysql query to get multiple rows of data from a table and insert into another table

  14. 14

    Insert multiple rows in one table based on number in another table

  15. 15

    How to insert multiple rows into one table for each id of another table

  16. 16

    insert data from one table to a single column another table with no relation

  17. 17

    Insert many rows from a table into one unique row in another table

  18. 18

    Insert (multiple) new rows into a table from another table using a subquery?

  19. 19

    How to insert multiple rows from a single query using eloquent/fluent

  20. 20

    Insert multiple values from one table to another using eloquent/query builder

  21. 21

    Insert Data from one table to another leaving the already existing rows

  22. 22

    Insert specific rows from one table in database into another with different columns

  23. 23

    Count number of rows from another table in single query

  24. 24

    INSERT rows multiple times based on a column value from another table

  25. 25

    oracle - How to insert unique values from a table to another multiple times?

  26. 26

    Insert multiple rows from two tables into one table

  27. 27

    Insert from another table, and update if key exists, possible in one query?

  28. 28

    Oracle Insert query from a table

  29. 29

    JQuery: How to drag multiple rows from one table to another?

HotTag

Archive