Re: Conditional data copy SQL Server to Oracle

D.Stone@xxxxxxxx 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/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

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

Relevant Pages

  • Re: IOT, memory and transaction time
    ... easily generate it with DBMS_METADATA.GET_DDL if your verison of Oracle ... were pretty consistent no matter how big the table (this transaction ... FOREIGN KEY (versionNo) REFERENCES T_TRANSACTIONS, ... Oracle provides read consistency and SQL Server ...
    (comp.databases.oracle.misc)
  • RE: SSIS (SQL Svr 2005) - Oracle Password Always Null
    ... Which task you are using to copy the table from Oracle to SQL Server? ... articles below to migrate the DTS package directly to see whether Oracle ... Upgrade from Data Transformation Services to Integration Services ...
    (microsoft.public.sqlserver.dts)
  • Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec
    ... "Writers block readers and readers block writers in SQLServer. ... getting around this fundamental issue and because of it SQL Server ... admitted they can happen in Oracle, I guess Oracle should never been used ... isolation level but you will always disagree because you follow doctorine ...
    (comp.databases.oracle.server)
  • Re: IOT, memory and transaction time
    ... easily generate it with DBMS_METADATA.GET_DDL if your verison of Oracle ... If it was the update statement that references ... FOREIGN KEY (versionNo) REFERENCES T_TRANSACTIONS, ... There are however differences how both products deal with concurrency; in Oracle readers don't get blocked while this may happen in SQL Server. ...
    (comp.databases.oracle.misc)
  • Re: Import problems on Windows Server
    ... XP is fine, so is Vista, like you I use them day in day out, around 12 hours ... a day myself too - laptops and the desktop i'm writing this on. ... I use SQL Server on XP and Vista for writing and demo'ing stuff, ... those are the same problems in the Oracle world. ...
    (comp.databases.oracle.server)