I am moving transactional data from our ERP (SAP) database to SQL Server (2K)for reporting purposes. The idea is to keep the reporting load off the transactional system. I am tring to keep the master data in SAP so I don't have to replicate all tables, just the big ones). I have set up a Linked server to the Oracel data.
I have a SQL Server (2K) table "custhist" with columns "custno" and "totalsales".
I have a linked server (Oracle/SAP) on the same server with table "custmaster" with columns "custno" and "custname" index on custno.
What is the best performing syntax to return all records from custhist for custno = "123" containing columns custno, custname and totalsales ?