OraFAQ Forum: Server Utilities » daily incremental loading

Home » RDBMS Server » Server Utilities » daily incremental loading

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

daily incremental loading [message #186382]

Mon, 07 August 2006 14:27

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!

Report message to a moderator

Re: daily incremental loading [message #186383 is a reply to message #186382]

Mon, 07 August 2006 14:36

Mahesh Rajendran
Messages: 10708
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

Barbara Boehmer
Messages: 9101
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.

Report message to a moderator

Re: daily incremental loading [message #186599 is a reply to message #186466]

Tue, 08 August 2006 12:42

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?

Report message to a moderator

Re: daily incremental loading [message #186600 is a reply to message #186383]

Tue, 08 August 2006 12:45

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!

Report message to a moderator

Re: daily incremental loading [message #186607 is a reply to message #186600]

Tue, 08 August 2006 13:04

Barbara Boehmer
Messages: 9101
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.

Report message to a moderator

Previous Topic:	Insert/Update facillity in loader
Next Topic:	problem while importing a dump with no rows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 12:49:47 CST 2024