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/library/aa933459(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
