Click here to Skip to main content
15,908,768 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two databases schema, one is an old database and 2nd is a new database both have same values and tables but in the new database in one table particular one column getting missing so the same column is in the old database also so I want to copy that column data which is in the old database to new database. how to do that in oracle.

there is one column is old database which is missing in 2nd database I want a query for copy data from one database to another database

What I have tried:

COPY FROM olddbusername/olddbpassword
 TO new/newpassword
  INSERT INTO contacts
(contact_id, last_name, first_name, address)
VALUES using select * from contacts
Posted
Updated 22-Jun-22 3:59am

more than likely the Contact_ID field has been created to be a uniqueidentifier. It is probably generated by the system.

Disabling that and then trying your insert

Also then reenable the auto increment for that ID or the generation.

Google is your friend for how to do that.
but it will be something like this.
alter table test_identity modify id generated by default as identity

Then your update/insert should work.
 
Share this answer
 
Comments
Suraj Ingle 22-Jun-22 9:54am    
Is there any other query to copy data from one database to another database, I tried to google it but was not able to find it, both databases have a different schema.
CHill60 22-Jun-22 10:58am    
"Then your update/insert should work." - not if there are different columns on each version of the contacts table, and not if the OP doesn't specify the database link
Firstly, is it safe to assume that you have set up a database link between the two databases?
If not then see CREATE DATABASE LINK[^]

Your code snippet
SQL
COPY FROM olddbusername/olddbpassword
 TO new/newpassword
contradicts your statement
Quote:
both have same values and tables
In any event note the following
Quote:
The COPY command will be deprecated in future releases of SQL*Plus. After Oracle 9i, no new datatypes are supported by COPY.
SQL*Plus COPY Command[^]

Looking at your other code snippet
SQL
INSERT INTO contacts
(contact_id, last_name, first_name, address)
VALUES using select * from contacts
Firstly, you should never use SELECT * when inserting into a table - list the columns explicitly.
Secondly, you will need to indicate which of the tables in is this database and which in the "other" database. So assuming you set up the database link as olddb you would need something like
SQL
INSERT INTO contacts
(contact_id, last_name, first_name, address)
VALUES select contact_id, last_name, first_name, address from contacts@olddb
Caveat: Untested code
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900