Delete from Oracle table based on IDs in a SQL table
- From: "patrick" <pgorry@xxxxxxxxx>
- Date: 11 Nov 2005 15:17:33 -0800
I have a requirement to delete some rows in an Oracle table (Oracle 9i)
based on a look-up from a sql table (SQL Server 2000). The following
query is the one I would like to use, utilising linked servers.
delete from ORACLEDS..MYSCHEMA.CUSTOMER
where CUST_ID in
(select cust_id from SQLCustTable)
Unfortunately we cannot use linked servers as the Oracle tables contain
NVARCHAR2 columns which SQL does not understand with any Oracle drivers
(ODBS, OLE DB etc..), although please correct me if I am wrong on this.
We do not have the ability to change the data-types in the Oracle
table.
I have been trying a few different solutions to no avail. So is it
possible to do it using any of the following:
1. Two seperate SQL Tasks. The first querying the SQL data source and
putting the IDs into a global temporary table. The second task using
the Oracle data-source but somehow being able to look at the SQL
temporary table
2. Similar to 1 but putting the IDs into a Global variable rather than
a temp table. Then using an ActiveX script to generate the SQL of the
second task dynamically by looping through the global variable. I have
a feeling this is possible but I do not like the solution so I have not
tried it yet!!
3. A Data Driven task, where the lookup returns multiple rows. I see
lots of examples on how to write activeX scripts to handle a lookup
with multiple columns, but none with multiple rows.
4. Any other method!!
Thanks in advance for your time.
Regards,
Patrick.
.
- Follow-Ups:
- Re: Delete from Oracle table based on IDs in a SQL table
- From: Allan Mitchell
- Re: Delete from Oracle table based on IDs in a SQL table
- Prev by Date: Re: List DTS pkgs
- Next by Date: add first step
- Previous by thread: Re: List DTS pkgs
- Next by thread: Re: Delete from Oracle table based on IDs in a SQL table
- Index(es):
Relevant Pages
|
