Conditional data copy SQL Server to Oracle

[D.Stone]

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

[DA Morgan]

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