JOIN Oracle and MSSQL tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

JOIN Oracle and MSSQL tables

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-09-30 : 11:13:25

We have a [scheduled] import from Oracle - it pulls all rows with a ChangeDate (at the oracle end) newer than the last time we ran it.

Its got out of sync for some reason, and I need to pull just the 150,000 rows that have a different ChangeDate (or don't exist at the MSSQL end)

Normally I do the transfer with:


SELECT @strSQL = 
'SELECT *
INTO	##TEMP_ORACLE_CUSTOMER
FROM	OPENQUERY(ORACLE, 
''SELECT *
FROM
(
SELECT
	CUSTOMER_ACCOUNT,
	CHANGE_DATE,
...
FROM	ORACLE.CUSTOMER
WHERE	(
	    CHANGE_DATE > ' + CONVERT(VARCHAR(20), @CHANGE_DATE) + '
	    OR (
		    CHANGE_DATE = ' + CONVERT(VARCHAR(20), @CHANGE_DATE) + '
		AND CUSTOMER_ACCOUNT > ''''' 
			+ COALESCE(@CUSTOMER_CUSTOMER_ACCOUNT, ' ') 
			+ '''''
	    )
	)
ORDER BY CHANGE_DATE
	, CUSTOMER_ACCOUNT
) X WHERE ROWNUM <= 10000
'')'

and then JOIN ##TEMP_ORACLE_CUSTOMER to my local Customer Table to sort out whats different.

I have a list of the 150,000 CUSTOMER_ACCOUNTs that I need in a table called TEMP_ORACLE_CUSTOMER_ACCOUNT (at the MSSQL end), and I'd like to pull just those from Oracle. I can't think how I'm going to JOIN that table to an OPENQUERY (without either pulling all 6 million rows from Oracle, or trying to make some massive IN('0000001', '0000002', ...) string

)

Any ideas?

Thanks

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2005-09-30 : 13:47:59

Even if you could do a join to action it the data would have to be transferred to one of the servers - better to control it yourself.
To do this I would send a series of queries to oracle to get the data - maybe 20 rows at a time using an in clause.

If you are holding the date in sql server for the next extract be careful as oracle may well hold a different date precision to that available in sql server and you can get extra or miss rows.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2005-09-30 : 14:02:36

OK, worst fears confirmed, thanks Nigel.

I was hoping there was some crafty way to do

SELECT *
FROM
OPENQUERY(ORACLE, 'SELECT ColList FROM OracleTable WHERE CustomerAccount IN (SELECT CustomerAccount FROM MSSQL_SERVER.MyDatabase.dbo.MyTopUpTable)')

"If you are holding the date in sql server ..."

Well ... the Oracle end's idea of a date, in this context, is "Number of seconds since 01-Jan-1970" stored as an INT - so I'm OK on that

, but thanks for the HeadsUp.

Kristen

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-09-30 : 14:57:48

you could try using a UDF and OPENQUERY(), then you could just join on the UDF in your SELECT. give it a shot and see if it works.

-ec

Kristen
Test

22859 Posts

Posted - 2005-09-30 : 15:13:56

Sorry EC I haven't grasped how you mean I should do that (syntax for OPENQUERY including a UDF), could you give me some PseudoCode pls?

Thanks

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-30 : 15:17:24

Maybe you could create a table on the Oracle side, and populate it with the customer accounts you are after with a DTS package. Then you could run the data extract there, and bring just the results you need back.

CODO ERGO SUM

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-09-30 : 16:45:55

the UDF example gets more complicated. A view would be much simpler. You could use a UDF to parameterize the view though, possibly achieving greater performance.

Anyway, just wrap your openquery/openrowset call in a standard TSQL view and join on the view. The next question is if that will perform adequately for your purposes.

-ec

Kristen
Test

22859 Posts

Posted - 2005-10-01 : 01:19:44

"create a table on the Oracle side"

The Oracle stuff is all third party, so that's not an option to me - even if I could remember how to do such a thing in Oracle Speak! It would work a treat though ...

EC - I reckon that is going to pull all the data from Oracle, make the JOIN, and then throw away what it doesn't need - but I could be wrong!

I did an analysis of the CHANGE_DATE of the missing rows, and they are mostly concentrated within a range where only about 50% of rows are missing so I'm going to pull that range first, which will get me 90% of my missing rows, then I can probably fiddle some IN('0001', '0002', ...) type syntax for the remainder.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources