Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 alternatives to linked server?

Author  Topic 

Starting Member

29 Posts

Posted - 2009-11-04 : 08:38:10
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
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)
Go to Top of Page

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.
Go to Top of Page

Not Just a Number

15586 Posts

Posted - 2009-11-19 : 21:58:20
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



Hint: Want your questions answered fast? Follow the direction in this link

Add yourself!

Go to Top of Page

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.
Go to Top of Page

- Advertisement -