473,386 Members | 1,785 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,386 software developers and data experts.

Conditional data copy SQL Server to Oracle

I wonder if anyone could offer some guidelines on the following
problem?

I need to transfer records from a SQL Server table to an 'equivalent'
Oracle table; however if the primary key for any record already exists
in the target table, the source record is skipped.

I want this process to be executed via an Access front-end to the SQL
Server.

What I'd roughed out was the following:

(1) The Access front-end contains a form with a button or simply a
menu option which fires off some VBA code
(2) The code runs a stored procedure on SQL Server back-end (I have
some sample code to do this - it basically uses ADO Command object)
(3) The stored proc calls sp_start_job to start a pre-created job on
the back-end which runs a DTS package

Sounds complicated, but hopefully each of 1-3 above is pretty minimal.

The DTS package itself can be set up to do the SQL Server -Oracle
table copy via the Designer GUI. Some minimal ActivX code seems
necessary to avoid copying records in the event that SITS already
contains the student. A 'transformation script' (see
http://msdn2.microsoft.com/en-us/lib...9(SQL.80).aspx) sounds
just the thing for this as it applies to the data being transferred on
a row-by-row basis.
The number of records to be transferred is not large (1000-2000), so
performance is not an issue.

Thanks for any feedback,

Dave
Jun 27 '08 #1
1 3681
D.*****@ed.ac.uk wrote:
I wonder if anyone could offer some guidelines on the following
problem?

I need to transfer records from a SQL Server table to an 'equivalent'
Oracle table; however if the primary key for any record already exists
in the target table, the source record is skipped.

I want this process to be executed via an Access front-end to the SQL
Server.

What I'd roughed out was the following:

(1) The Access front-end contains a form with a button or simply a
menu option which fires off some VBA code
(2) The code runs a stored procedure on SQL Server back-end (I have
some sample code to do this - it basically uses ADO Command object)
(3) The stored proc calls sp_start_job to start a pre-created job on
the back-end which runs a DTS package

Sounds complicated, but hopefully each of 1-3 above is pretty minimal.

The DTS package itself can be set up to do the SQL Server -Oracle
table copy via the Designer GUI. Some minimal ActivX code seems
necessary to avoid copying records in the event that SITS already
contains the student. A 'transformation script' (see
http://msdn2.microsoft.com/en-us/lib...9(SQL.80).aspx) sounds
just the thing for this as it applies to the data being transferred on
a row-by-row basis.
The number of records to be transferred is not large (1000-2000), so
performance is not an issue.

Thanks for any feedback,

Dave
I can't think of much worse than front-ending Oracle with Access. Why
not just go direct from Oracle to SQL Server? And look at Oracle's
MERGE statement:
http://www.psoug.org/reference/merge.html
The docs on it are at http://tahiti.oracle.com.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
da******@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Jun 27 '08 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Will | last post by:
On the subject of Data Warehouses, Data Cubes & OLAP…. I would like to speak frankly about Data Warehouses, Data Cubes and OLAP (on-line analytical processing). Has it dawned on anyone else...
1
by: sbh | last post by:
I need to copy data from a table on one Oracle server to another. Scenario: Need to create a stored procedure in server a, database aa that will copy data from server b, database bb, table bbb to...
3
by: David Link | last post by:
Hi All, Here's a Conditional drop_table func for those interested. There was a thread on this a long time back. We do this all the time : DELETE TABLE sales; CREATE TABLE sales (...);
10
by: Andrea M. Segovia | last post by:
Hello, I am a newbie to Oracle databases.... We have a visualization front-end tool connected to an Oracle back-end database on a Tru64 UNIX server. We also have clients with MS access...
1
by: Andrew Arace | last post by:
I scoured the groups for some hands on code to perform the menial task of exporting table data from an Access 2000 database to Oracle database (in this case, it was oracle 8i but i'm assuming this...
6
by: Marc Jennings | last post by:
Hi there, I am trying to get some conditional functionality into some of my apps. I can use the format : private void doStuff() { //do something }
3
by: Lyners | last post by:
I am having a hard time with this one, and I thought it would be easy. I have a datagrid in which I have textboxs for users to enter data. One of the fields in the database behind the datagrid...
5
by: petro | last post by:
Hello all, My asp.net web application works on my machine but I get the following error on our test web server, There is only one oracle home on the test server. Does anyone know how to resolve...
8
by: Lykins | last post by:
We currently use Access 2003 in our company and have had this issues from every version from Access 97 to 2003. We deal with large databases and run a lot of queries over tables with millions of...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.