Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lordzoster
Starting Member
29 Posts |
 Posted - 2009-11-04 : 08:38:10
|
| HalloI 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
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-11-19 : 19:20:50
|
| Replication doesn't work for your environment?=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-11-19 : 21:02:21
|
| 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. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-11-19 : 21:58:20
|
| Disagee here with the MVYIf it's that little amount of data, I would1. Create an extract out of Oracle in |~| delimited form2. bcp it into a tale in SQL ServerThere'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 wayBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-11-19 : 23:37:55
|
| 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. |
|
|
|
|
|
|
alternatives to linked server?