Hi all...

I need to do the following:

I have a table in SQL Server 2000 that has some data. One of them is a
company's code.

To retrieve that data I have this query:

SELECT CODEMPLEADOR
FROM TARJETA T, EMPRESA E
WHERE TAR_ESTADO = 'I'
AND E.EMP_RUT = T.EMPRESA_RUT


Using those codes I have to query an Oracle database using this query:

select distinct c.numid, c.dvid, t.nomter, t.apepater, t.apemater, t.apeter
from poliza p, cliente c, tercero t
where p.stspol = 'ACT'
and p.codempleador = :EMPLEADOR
and p.codcli = c.codcli
and t.numid = c.numid
and t.dvid = c.dvid
and t.tipoid = c.tipoid

where :EMPLEADOR is each code returned by he SQL Server query.

Finally, I have to insert the data returned by the Oracle query into other
SQL Server table.

Graphically, I have to do this:

SQL SERVER SELECT QUERY ---> ORACLE SELECT QUERY ---> SQL SERVER INSERT INTO

Can I do this using lookup tables or something similar?

Thanks a lot in advance
Jaime

Consider using Access as a front-end and conduit between SQL Server and Oracle.
It works great in such matters, without all the DTS headaches.
It'll get the job done quickly and easily and buy you some time until you figure
out
and implement a DTS solution.

GeoSynch


"Jaime Stuardo" <JaimeStuardo (AT) discussions (DOT) microsoft.com> wrote

Jaime Stuardo wrote:
Can you create an ODBC connection on the SQL Server to the Oracle database? If
so, create a view in the Oracle database with the proper table joins. Use the
ODBC connection as the source in your DTS package.

INSERT INTO SQLtable2
(numid, dvid, nomter, apepater, apemater, apeter)
SELECT numid, dvid, nomter, apepater, apemater, apeter
FROM OracleView
WHERE OracleView.codempleador =
(SELECT CODEMPLEADOR
FROM TARJETA T, EMPRESA E
WHERE TAR_ESTADO = 'I' AND E.EMP_RUT = T.EMPRESA_RUT)