469,286 Members | 2,425 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,286 developers. It's quick & easy.

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 3539
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by David Link | last post: by
1 post views Thread by Andrew Arace | last post: by
6 posts views Thread by Marc Jennings | last post: by
3 posts views Thread by Lyners | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.