SEO

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

D.Stone@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
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org