copy a table

> So what do you do if you have a new record to insert to the target table, and it has the same PK value as a record already in the table?

Thank you all. Slimdave, you brought up an excellent point and it helped me discover something. Here in this case, it won't allow duplicates and I found out that some records will violate P.K here. Therefore, a direct copy can't be done and so initially they are loaded onto a flat file. Please let me know why you asked about P.Ks and has it something to do with your answer before? Thanks again.

I asked because it seemed like a likely scenario, and it means you can't just do an insert, you have to check for matching PK's.

So where the same PK exists in each table, does that really mean it's the same record, or is the PK some kind of non-meaningful sequence number?

Hi,

In this case the backup database gets records from two other databases. Here, the records (data) in the two sources are different but, they have similar pks. Ex: P.K 10000 is in Source A as well as Source B. Target already has 10000 from Source A. Therefore, it won't accept 10000 from Source B. I haven't use any import utility so far.
I am thinking to load Source B records to a flat file/ dump, change the Source B P.Ks. Eg: 10000 to B10000. Therefore, eliminating duplicate problem. This is a onetime procedure. However, if an automatic process were to be mandatory, my original question still remains. Import-Export OR SQL*Loader OR SQL*PLUS copy utility?? I can't do any trial & error.

Thanks.

Quote:

---

Originally posted by pvnad1
Hi,

In this case the backup database gets records from two other databases. Here, the records (data) in the two sources are different but, they have similar pks. Ex: P.K 10000 is in Source A as well as Source B. Target already has 10000 from Source A. Therefore, it won't accept 10000 from Source B. I haven't use any import utility so far.
I am thinking to load Source B records to a flat file/ dump, change the Source B P.Ks. Eg: 10000 to B10000. Therefore, eliminating duplicate problem. This is a onetime procedure. However, if an automatic process were to be mandatory, my original question still remains. Import-Export OR SQL*Loader OR SQL*PLUS copy utility?? I can't do any trial & error.

Thanks.

---

Forget abt sql copy. Dont make Ur database wait for "SQL*Net more data to client" event.

Exp/Imp ... since, U have 2 columns in different order, this is not teh right method. U will have to do many manual steps after exp/imp, which is not suitable for automation. Offcourse, you can always write scripts that take care of post imp. steps.. But, it's a waste of time.

sql*load is better than other too. U can write nice shell script to spool the data to flatfile, transfer it to other server and then use another script to load the data.

I like the idea of a db link from the target back to the source. It lets everything run from within the database, and will allow you to run comparisons of the source and target data sets as part of the transfer process.