SQL Server Linked Server + Query using existing recordset 1

[robfromuk]

Environment: SQL Server 2000, Linked server setup to an Oracle 9i DB.

I have a results set in SQL Server. It has a column called pid.

pid has many more entries in the Oracle DB (also in a column called pid), spanning across many dates. The one I have in SQL Server is just one of those dates.

I would like to retrieve ALL dates associated with each pid from the above existing results, querying in Oracle and place them in a new table in SQL Server.

Additional information (though may not be so relevant): The original results set in SQL Server was taken from the Oracle DB.

Can anyone suggest a way to perform this? I was thinking some complex use of cursor but wasn't sure if this was possible through the linked server mechanism. Not sure why I added that note ... ANY solution is most welcome!

Sincere thanks in advance!

 

[JamesLean]

Something like this?

INSERT target_table (pid, date)
SELECT t2.pid, t2.date
FROM table1 t1
  JOIN oracle_server..owner.table t2 ON t1.pid = t2.pid

--James

 

[robfromuk]

Much appreciated James! I have another problem now (

http://www.tek-tips.com/viewthread.cfm?qid=1069729&page=1)

but at least this one is out of the way!

Thank you again.