SELECT *
INTO #TEMP
FROM OPENQUERY(MyLinkedOracleServer, 'SELECT Col1, Col2, ... FROM OracleDatbase.OracleTable WHERE ...')

quote:

---

Originally posted by Kristen

I would do:

SELECT *
INTO #TEMP
FROM OPENQUERY(MyLinkedOracleServer, 'SELECT Col1, Col2, ... FROM OracleDatbase.OracleTable WHERE ...')

and then JOIN #TEMP to the appropriate local tables and make my update.

Make sure that you Oracle Query is carefully optimised (if that is the slow part)

---

select * from openquery(MyLinkedOracleServer,'SELECT Col1,Col2, ... FROM OracleDatbase.OracleTable)

quote:

---

Originally posted by pwvailla

Not sure this answers the question, but...

Where does the #TEMP table get created? I have no ability to create on the Oracle environment. I am needing to reference a lookup table on SQL Server 2005 database on another server environment (my WHERE clause will require me to SELECT from Oracle ERP where a value EXISTS in a SQL environment). That is possible?

quote:

---

Originally posted by Kristen

I would do:

SELECT *
INTO #TEMP
FROM OPENQUERY(MyLinkedOracleServer, 'SELECT Col1, Col2, ... FROM OracleDatbase.OracleTable WHERE ...')

and then JOIN #TEMP to the appropriate local tables and make my update.

Make sure that you Oracle Query is carefully optimised (if that is the slow part)

---

---

SELECT po_header_id, segment1, vendor_id, vendor_site_id
FROM gpspgjp1.PO.PO_HEADERS_ALL AS POHA
JOIN dbo.VALID_PO_PROJECTS
    ON dbo.VALID_PO_PROJECTS.segment1 = POHA.segment1

quote:

---

You could also use dynamic to construct a list of the values for segment1 in VALID_PO_PROJECTS and pass it as a "list" using an IN clause

---

quote:

---

but if the IN clause is large I think that will be unworkable too

---

quote:

---

Originally posted by robvolk

quote:

---

You could also use dynamic to construct a list of the values for segment1 in VALID_PO_PROJECTS and pass it as a "list" using an IN clause

---

I second this, I had to do the same thing with an Oracle data warehouse a number of years ago. It's aggravating but the performance is about as good as you'll get.

quote:

---

but if the IN clause is large I think that will be unworkable too

---

I had to break up my IN list into 1000-2000 element chunks and run multiple statements to get them all, but it worked and was quite fast. It's difficult to do as dynamic SQL using OPENQUERY, my recommendation is to generate all the SQL to a script file and run the script via the sqlcmd utility, or run it manually. OPENQUERY will probably give better performance than using 4-part references.

---

INSERT INTO #TEMP
SELECT * FROM OPENQUERY(gpspgjp1, 'SELECT po_header_id, segment1, vendor_id, vendor_site_id
FROM PO.PO_HEADERS_ALL WHERE PO.PO_HEADERS_ALL.segment1 IN(1001,1002,1003,...,2000)')

quote:

---

Originally posted by robvolk

Essentially you build a series of SQL statements like so:

INSERT INTO #TEMP
SELECT * FROM OPENQUERY(gpspgjp1, 'SELECT po_header_id, segment1, vendor_id, vendor_site_id
FROM PO.PO_HEADERS_ALL WHERE PO.PO_HEADERS_ALL.segment1 IN(1001,1002,1003,...,2000)')

You'll replace the portion in red with a list of segment IDs from DBO.VALID_PO_PROJECTS. I'd recommend starting with 1000 IDs at most and seeing what the performance is like. If the Oracle table is indexed on that column then pulling only 1000 at a time should be quick, even if there are millions of rows. You can then try 2000, 5000 or even more, but you'll reach some practical limit short of your total of 50,000.

I'd recommend building these statements by hand and running them before worrying about automating or generating it dynamically. You can do SELECT TOP 1000 segment1 + ',' FROM DBO.VALID_PO_PROJECTS and pasting the results into the red portion of the query above. If you find that 1000 take to long, reduce it to 500, or 200 or whatever provides reasonable performance.

---

SELECT TOP 1000 '''''' + segment1 + ''''',' FROM DBO.VALID_PO_PROJECTS

quote:

---

Originally posted by robvolk

Yes, OPENQUERY runs it on the Oracle box and returns the results. There is minimal overhead compared to using 4-part names with a JOIN. The SQL does need to conform to Oracle syntax, but what you're using here is mostly identical to SQL Server, and you're generating most of it anyway.

Try this to get the correct quotes on the varchar data:

SELECT TOP 1000 '''''' + segment1 + ''''',' FROM DBO.VALID_PO_PROJECTS

Paste those results into the query as I described earlier.

---

INSERT INTO dbo.PO_LINES_ALL
SELECT * FROM OPENQUERY 
(gpspgjp1,
'SELECT
po_header_id,
po_line_id,
line_num,
unit_price,
quantity,
unit_meas_lookup_code,
item_description,
attribute3,
attribute4,
attribute6,
attribute5,
closed_flag,
closed_date,
cancel_flag,
cancel_date
FROM po.po_lines_all')