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

问题:

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.

回答1:


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'
 ;

回答2:


I think that this variant is right for you.

declare
 v_DCRDI number;
begin
select Max(DCRDID)+1 into v_DCRDI from dcr_details_new;
INSERT INTO dcr_details_new 
SELECT v_DCRDI+rownum DCRDI, TWNCODE,'100008',DOCCATOGARY,
DCR_NO,VISIT_NO,GIVEAWAY,COMPETITORBRN,REMARK,DCRDRDATE,COM_ACTI
FROM dcr_details_old  
WHERE DOCREGNO= 'T10037' /*and DCR_NO = 28766*/;
end;

回答3:


I'm pretty sure all you're after is a single insert statement along the lines of:

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,
         twncode,
         '100008',
         doccatogary,
         dcr_no,
         visit_no,
         giveaway,
         competitorbrn,
         remark,
         dcrdrdate,
         com_acti
  FROM   dcr_details_old;

Using "max(val) + some_val" to generate new primary key values is, in general, a bad idea. If this is a static data table that is only ever inserted into by release scripts, then it's just about acceptable. Personally, I'd still rather use a sequence!

I would highly recommend that you create a sequence that has a starting value of the current max val + 1 (or some other value), and then use new_seq.nextval when adding rows into the table. Much simpler, and way more foolproof!

回答4:


After all your help this is the final answer worked for me.

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,'100008',DOCCATOGARY,DCR_NO,VISIT_NO,GIVEAWAY,
COMPETITORBRN,REMARK,DCRDRDATE,COM_ACTI
from dcr_details_old where DOCREGNO = 'T10037' ;

Thanks everybody

标签: sql-insert unique-constraint ora-00001
© 2014 TuiCode, Inc.