how to load data into a table across different databases
HI,
I have two same structured tables in two different schemas in two different databases in different networks, environments. Say one is table is in testing environment other table is development environment. What is the easiest way to load the changes that made in testing environment table to the development environment table. Any ideas, sample code, resources, links highly appreciated. Thanks in advance
I have two same structured tables in two different schemas in two different databases in different networks, environments. Say one is table is in testing environment other table is development environment. What is the easiest way to load the changes that made in testing environment table to the development environment table. Any ideas, sample code, resources, links highly appreciated. Thanks in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
do you have the same user and password on both the target and source?
if so, you don't need to create a link explicitly. you can do the inserts as Milleniumaire showed above but where you have the dblink name you instead just use the tnsconnect string.
insert into my_table_on_test
select * from my_table_on_dev@XXX;
note, this ONLY works if the user you are running the insert also exists on the source system with the same password
if so, you don't need to create a link explicitly. you can do the inserts as Milleniumaire showed above but where you have the dblink name you instead just use the tnsconnect string.
insert into my_table_on_test
select * from my_table_on_dev@XXX;
note, this ONLY works if the user you are running the insert also exists on the source system with the same password
There is also the copy command in SQL*Plus.
You could use export and import. These operating system commands allow you to export data from a table, schema or database into a file and then import that data into another database.
On your dev system at the os command line (assuming you are using unix) type:
exp
then enter the username password of the user owning the tables. Follow the prompts to export a single table. You will then have a file with a .dmp extension. Copy this binary file to the machine on which the test database resides and at the os prompt type:
imp
again, follow the prompts and the data in the .dmp file will be loaded into the relevant tables.
Be aware that the data will be appended to any other data that already exists in the tables. Ideally, the schema owners (Oracle accounts) should be the same on dev and test to avoid requiring special privileges.
If you have access to a development tool called Toad it is also possible to create "insert" scripts from table data, which you can then run against another database. To do this, right click on a table and chose Export Data.
On your dev system at the os command line (assuming you are using unix) type:
exp
then enter the username password of the user owning the tables. Follow the prompts to export a single table. You will then have a file with a .dmp extension. Copy this binary file to the machine on which the test database resides and at the os prompt type:
imp
again, follow the prompts and the data in the .dmp file will be loaded into the relevant tables.
Be aware that the data will be appended to any other data that already exists in the tables. Ideally, the schema owners (Oracle accounts) should be the same on dev and test to avoid requiring special privileges.
If you have access to a development tool called Toad it is also possible to create "insert" scripts from table data, which you can then run against another database. To do this, right click on a table and chose Export Data.
ASKER
thank you
glad we could help
ASKER