Home » RDBMS Server » Server Utilities » daily incremental loading
daily incremental loading [message #186382] Mon, 07 August 2006 14:27 Go to next message
catpurr
Messages: 11
Registered: November 2005
Junior Member
I am involving with a project to do daily incremental transfer data from SQL server to Oracle. I will get flat SQL server exort file (flat file). How can I find out new/updated data under windows environment? what's the best way to automate the loading? Thanks!
Re: daily incremental loading [message #186383 is a reply to message #186382] Mon, 07 August 2006 14:36 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You cannot. There is no default method.
One workaround is to load data "as-is" into a staging table (or use external table. Consider the text file as an Oracle Table)
Use sql means to compare data in staging table with the original table ( merge statement or something like that).

[Updated on: Mon, 07 August 2006 14:37]

Report message to a moderator

Re: daily incremental loading [message #186466 is a reply to message #186382] Tue, 08 August 2006 01:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You could use an external table, so that when the flat file is overwritten, the external table then automatically contain the new data. Then you could write a procedure to distribute the data from the external table and use dbms_job to schedule the procedure to run daily.
Re: daily incremental loading [message #186599 is a reply to message #186466] Tue, 08 August 2006 12:42 Go to previous messageGo to next message
catpurr
Messages: 11
Registered: November 2005
Junior Member
thanks! but when flat file is overwritten, is it still going to be next day full data? how can I figure out the new data (incremental) and existing data from the full data?
Re: daily incremental loading [message #186600 is a reply to message #186383] Tue, 08 August 2006 12:45 Go to previous messageGo to next message
catpurr
Messages: 11
Registered: November 2005
Junior Member
SQL means? Can you suggest someplace i can get more info for that? or it will be great if you provide some samply code/procedure? Thanks!
Re: daily incremental loading [message #186607 is a reply to message #186600] Tue, 08 August 2006 13:04 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
As Mahesh Rajendran suggested, you can use the SQL MERGE statement to insert new records and update old ones. Here is a link to a section of the online documetation about MERGE:
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9016.htm#SQLRF01606

So, what you need to do is:

1. Create an external table that uses the text file.

2. Create a procedure that uses the sql merge statement to insert the new records from the external table to the table of full data and update existing records.

3. Use dbms_job.submit to schedule the procedure to run once per day.
Previous Topic: Insert/Update facillity in loader
Next Topic: problem while importing a dump with no rows
Goto Forum:
  


Current Time: Thu Dec 08 16:18:17 CST 2016

Total time taken to generate the page: 0.16571 seconds