Add rows to an existing table 1

[seyfert]

Frist off -
Greetings to all of you. I just found this forum, but the advice here seems awesome. I hope not to need you often, but that is never the case with me.

I'm using Enterprise Manager with a schema already created for a table (gmu_2006). I want to add rows to that table using a .dmp file.

When I use the following line: "imp gmu_2006/gmu_2006 file=auditor_fabinvh_FY062.dmp log=GMU_FABINVH_APPEND.log grants=no fromuser=FIMSMGR touser=gmu_2006",

I get this error:
"IMP-00015: following statement failed because the object already exists: "CREATE TABLE "FABINVH" ("FABINVH_CODE" VARCHAR2(8) NOT NULL ENABLE, blah, blah, blah."

Is there a way to just import the rows that are in the file?

Thanks for any help you may offer.

 

[SantaMufasa]

Seyfert, a hearty "Welcome" to our Tek-Tips Oracle Family!

Yes, you can do as you wish by adding one more parameter to your "imp" command line:

ignore=Y

This means "Ignore the pre-existence of objects (e.g. tables)...Do not throw errors if the table already exists."

Let us know if this resolves/answers your question.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[seyfert]

Thank you for your reply.

I tried the ignore=y and successfully got 1618 rows to be added. However 14 rows did not import. I got this error message:

"IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (GMU_2006.PK_FABINVH) violated"

Not surprisingly, I got this error 14 times.

 

[SantaMufasa]

Seyfert, that is because 14 of the rows that exist in your .dmp file have primary key values that attempt (unsuccessfully) to duplicate values that already exist in the primary key column(s) of your "gmu_2006" table. You wouldn't want primary key duplicates in your table.

Can you a) discern which of the 14 rows are duplicates and b) articulate what you want to do with those 14 rows? Since their primary keys are duplicates, either a) their data are extraneous and you can ignore them, or b) their data are significant, need to be in the database, but you must reconcile the duplicate-primary-key issue by either creating a unique value for the primary keys of those 14 rows, or update the existing rows with the "correct" data that reside in the 14 rows.

Let us know what you decide.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Turkbear]

Hi,
That means that in your import file there were 14 records whose data in a field that is defined as a Primary Key was duplicated..

Either disable the PK ( but, if you have one it is probably needed) or correct your imp file..

I would, in the alternative, create a new table for the imported data and use a :

Insert into existing_table select * from imported_table
where inserted_table.keyfield not in ( select key_field from imported_table);

Or look at the MERGE command (if your version has it, not sure when it was added)







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[seyfert]

Thank you all. It seems this has created a lot of work for some other people in my office. They need to check on some things and get back with me.

It is possible that those 14 rows were already part of the database.

 

[SantaMufasa]

Absolutely, Seyfert...if not the entire row from each of the 14 instances, at least their Primary Keys already exist. Ergo, my suggestion earlier:

...either a) their data are extraneous and you can ignore them, or b) their data are significant, need to be in the database, but you must reconcile the duplicate-primary-key issue by either creating a unique value for the primary keys of those 14 rows, or update the existing rows with the "correct" data that reside in the 14 rows.

Let us know what you discover.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[seyfert]

I haven't heard back from anyone regarding the information in the 14 rows, but I am wondering...

If I need to change the data in those rows, how would I do it?

BTW-you all have been very helpful. My office was quite impressed that I(you) came up with a way to add the new data without having to delete all the old stuff. Thank you.

 

[SantaMufasa]

Frankly, Seyfert, you can certainly do updates using primary key values, but if I were doing the updates, and since there are perhaps different columns with different data for each of the 14 rows that could change, this is how I tend to make such changes:

1) Identify the Oracle rowids of the 14 rows (matching the duplicate PKs). (Oracle rowids are unique for rows across the entire database.)
2) Update each row's specific values using the appropriate rowid that you obtained from step 1, above.

I shall illustrate the proof of concept using a scenario where my primary key column has the name "ID" and instead of 14 rows, I shall modify two rows whose PK values are "16" and "21" (since by the second example, I'm sure that you will get the idea):

select id,rowid, last_name, salary, commission_pct
from s_emp
where id in (16,21)
order by id;

ID ROWID              LAST_NAME                     SALARY COMMISSION_PCT
-- ------------------ ------------------------- ---------- --------------
16 AAADOlAAEAAAAEiAAP Maduro                          1400
21 AAADOlAAEAAAAEiAAU Markarian                        850

update s_emp
   set last_name = 'Garcia', commission_pct = 18.5
   where rowid = 'AAADOlAAEAAAAEiAAP';

1 row updated.

update s_emp
   set salary = 1325
   where rowid = 'AAADOlAAEAAAAEiAAU';

1 row updated.

commit;

Commit complete.

select id,rowid, last_name, salary, commission_pct
from s_emp
where id in (16,21)
order by id;

ID ROWID              LAST_NAME                     SALARY COMMISSION_PCT
-- ------------------ ------------------------- ---------- --------------
16 AAADOlAAEAAAAEiAAP Garcia                          1400     18.5
21 AAADOlAAEAAAAEiAAU Markarian                       1325

Let us know if this satisfactorily illustrates a resolution for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[seyfert]

I can't believe how helpful you are. Thank you so much Mufasa. (I just love the way Whoopie Goldberg says, "Mufasa" in The Lion King)

It is a lot of work if I have to update the rows that way, but it might be our only choice. This is a new procedure for our office and I'm sure the wrinkles will get ironed out.

 

[DeepDiverMom]

Seyfert,

Yes, Mufasa has been extremly helpful to me too, and similar to Ebay there is a way to leave positive feedback. I always remember to give Mufasa (and other helpful Tek-tipsters) a Purple Star...it sounds like you would like to give him one too. The way you do that is by clicking the link at the end of one of Mufasa's replys, "Thank Mufasa for this valuable post!"

Remember payment for a job well done on Tek-tips is Purple Stars...along with the cudos you have given.

Mom

 

[seyfert]

Leave it to a mom to instruct on proper protocol in such a nice way. Thanks mom.