copy data between two database

[charon]

hi,
Due to there are two different databases(access and oracle) located at different place, one is at our Web Hosting Company(access), another one is located at our intranet(oracle), so in order to get the data from the remote database, I need to connect to the remote site and get the data, after that retrieve it and copy it to our oracle database via coding.

Is there any better way??????

[whammy]

Not from what you described... that sounds just about right. ;)

No matter what, you would likely have to parse the data you're receiving in some fashion; it's just up to you to be able to figure out how to do it the best way. I'm no expert into communicating between Oracle and Access, although it's not a problem between Access and SQL Server... ;)

[david7777]

Are the databases the same? Contain the same data, have the same schema? If so - you can write a little asp to do the work for you. Just make the code check for all records that are in the access database, but not in the oracle one, then put those in a recordset, and update the oracle database with the records... This means that you will only write the code once, and can run it when ever you want :)

[charon]

yup, they contain same data and schema.
now what i'm going to do are:

1.) find out (by doing research) how to remore access my access database

2.) how to direct parse and transfer the data from access to oracle and visa versa effectively.

for me if using ASP/JSP, what i can do is, as david777 said, connect to remote database and put those in a recordset, and update the oracle database with the records. But I have to do validation , that is check whether the destination db already have the records, if not, then only transfer the records.
But would it takes longer time?? effective????

Is there any SQL statement which allow us to direct copy the data of one database to another table of database (differents database)??

[raf]

Sure. Check out the acces helpfunction on insert into
INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
____SELECT [source.]field1[, field2[, ...]
____FROM tableexpression
If you have the right drivers, and all variables are defined correctly in both tables, then this should work.
There are db's that support some sort of refreshing, which means that existing records will be updated and new ones will be inserted.

But it would really surprise me if access would support that. You can build it yourself by copying your table to a temporarely table, running a "find unmatched query", then insert all unmatched records, then run an update on the matched querys.

Probably there will be a more structural or safer sollution. Cause by running this sort a querys through embedded sql (in ASP or PHP) you'll need to store the oracle in a directory with write permissions for a webclient ... And you'll need to store the user and pwd somewhere in your site-directory ... + Might be easier to just dump the data in a csv or xls file or copy the accesfile or the tables you need, put it on a jazz or cd-rom and update the oracle from an intranetmachine

[whammy]

I've done this kind of thing plenty with text files to import into SQL Server or Access, usually what I'd do is create a temporary table, import all of the records into that, and then mark the ones I already have in the "live" table (perhaps mark an additional field with an "X" or something - it doesn't really matter how you do it, as long as you know by this technique which fields are already in your live database!).

Then you'd update the fields with an X depending on a WHERE clause or whatnot and append the others to your database.

[raf]

Also,

In the table you're updating, have a variable that is set to 1 or the time or timer or whatever after it's updated.
If the updateprocess stops halfway (due to a db-problem, serverproblem, etc) then you'll know which records where already updated. I've seen it more then once that this sort a stuff happens, and that some records get updated twice. (which can be very hard to reset, and is a big problem if the update is something like variable=variable+thismonthsalary).
(+ the X whammy suggested in the "copy of the table" table)
You could even store all records (or just the ID of these records) in a table and the next time you run the update, join with this table first, mark the records from the "copy of the table" that can be matched with this ID table, and only do a 'find unmatched' query with the oracle, for the remaining records (the ones without the X). This will take less resources from the oracle and will be even faster if you have a big number of records (i mean hundreds of thousands, or miljons).

[charon]

Thanks so much.
But I'm now going to do some research on which method is most effictive, use direct access to remote server and copy the data directly or create .csv / xls files??

1.) Will it be not save if I use remote access since I need to supply all the username and password. If yes, is there any solution??? we have our own proxy server and firewall

2.) If I use .cvs/text files, will it be difficult for the user (she/he will have to get the .csv/xls and update to the oracle database)

3.) I don't know how to create the the files, anyway I will look for it.:)) I like to do something that I never try, so that i won't get bored

[raf]

1.) Will it be not save if I use remote access since I need to supply all the username and password. If yes, is there any solution??? we have our own proxy server and firewall
If you have a db running on a machine that is connected to internet, there's always a risk. I know that at the companys if work(ed), they have a very strict segmentation between intranet and internet. But i don't know how strict your security is and how sensitive the info from your db is. But remote access will imply you need to store a username and password somewhere on your hosts machine (and maybe he's not strict enough?)
Of coarse, you could limit the permissions of that webuser-oracle account etc

2.) If I use .cvs/text files, will it be difficult for the user (she/he will have to get the .csv/xls and update to the oracle database)
Hey?! What sort a users? DBA's? In any case, this uploading should be automised as far as possible (javaservlets or some batchprocess or something like that, with automatical checks that count the number of new records, number of updated records etc + compare them with pre-fixed borders) + the DBA('s) for the two db's should ideally be the same, or there should be someone that overlooks them. Changes in the design of one of the db's could otherwise create serious problems. Integrating and periodically updating of db's (certainly oracle and access) is quite tricky and requires that you document the extraction and loading processes