Hi all....

I'm programming a DTS package that transfers data from an Oracle database to
a SQL Server 2000 table.

Since some queries are heavy, I have separated them in more queries that I
execute one after the other.

This is the execution scenario. All steps are Transform Data Tasks from
Oracle connection to SQL Server connection. Target SQL Server connection
affects only 1 table, named, for example, TARGET_POLIZA whose primary key is
NUMPOL (TARGET_POLIZA structure is NUMPOL, F1, F2, F3, F4)

Step 1: SELECT P.NUMPOL, SUM(T1.FIELD1) F1, SUM(T1.FIELD2) F2,
SUM(T1.FIELD3) F3
FROM POLIZA P, TABLE1 T1
WHERE T1.NUMPOL = P.NUMPOL
GROUP BY P.NUMPOL

Step 2: SELECT P.NUMPOL, COUNT(T2.FIELD1) F4
FROM POLIZA P, TABLE2 T2
WHERE T2.NUMPOL = P.NUMPOL
GROUP BY P.NUMPOL

I want Step 1 to be an INSERT to TARGET_POLIZA of SQL Server connection, but
Step 2 to be an UPDATE to TARGET_POLIZA using NUMPOL as primary key.

How can I accomplish this?
Thanks a lot in advance

Jaime

So there is no issue with the first requirement right? You just want to
pump data.

The issue is with the update.

Number of options stright off

1. OPENDATASOURCE()
2. Linked Servers
3. Move all the data in Query 2 from Oracle to SQL Server (into a
working table) and then use TSQL to do some updates etc.


Allan

"Jaime Stuardo" <JaimeStuardo (AT) discussions (DOT) microsoft.com> wrote in
message news:2CE9FEB7-CB3A-410E-8768-E2E81E4901B5 (AT) microsoft (DOT) com: