SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 alternatives to linked server?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lordzoster
Starting Member

29 Posts

Posted - 11/04/2009 :  08:38:10  Show Profile  Reply with Quote
Hallo
I need to periodically copy from an Oracle 9i db only new and changed rows into an SQL2K STD one, to be used for some online reports: the source table is a ~20K rows, growing 300rows a day, and I should perform the refresh every ~15 minutes.
I only have read access to the Oracle db, hence cannot set up a replica.

Is there any other way to trace newly created and changed rows, so that I can copy only a minimal subset of rows from the source?

Thanks in advance

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1770 Posts

Posted - 11/19/2009 :  19:20:50  Show Profile  Reply with Quote
Replication doesn't work for your environment?

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 11/19/2009 :  21:02:21  Show Profile  Visit robvolk's Homepage  Reply with Quote
Does the source table have a datetime/timestamp column that gets updated whenever the data is changed (i.e. createdate or modifieddate)? If so you could find the maximum value of this column in your own replica, then craft a query to pull only the data that is later than that date from the source.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/19/2009 :  21:58:20  Show Profile  Reply with Quote
Disagee here with the MVY

If it's that little amount of data, I would

1. Create an extract out of Oracle in |~| delimited form

2. bcp it into a tale in SQL Server

There's no confusion about what new (INSERTS) what's gone (DELETEs), and what's been modified (UPDATEs)

You'll save yourself a bunch of overhead this way



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 11/19/2009 :  23:37:55  Show Profile  Visit robvolk's Homepage  Reply with Quote
Well, he's trying to get the minimal amount of data from Oracle. Granted 20K rows is not a lot to grab and find differences, but if there's a reliable way to pull only the 300 that are needed that would be better. Especially if it has to run every 15 minutes.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000