SQL Statement Joining Oracle and Ms SQL Server

SQL statement joining Oracle and MS SQL Server

Yes- both Oracle and SQL Server support the linked server concept. That allows you to reference the other server using a 4 part name. For example:

select  *
from    LocalDb.Schema.Table
cross join
        OracleLinkedServer.RemoteDb.RemoteSchema.RemoteTable

Inner join query from SQL Server and Oracle with Python

Simply, you cannot run a query across remote databases as if they are local tables. Within each cursor of a database connection, its universe is limited to the connecting server, schema, or catalog/database you specified. Only those objects (tables, functions/procedures, etc.) are available. Nothing beyond is recognized.

With that said, most relational databases today now support external server connections including Oracle's Database Links, SQL Server's Linked Servers, DB2's Catalog, Postgres' Foreign Data Wrappers, MySQL's Federated Storage Engine, MS Access' Linked Tables, SQLite's ATTACH (but only other SQLite databases).

Therefore, consider establishing a remote connection either in Oracle or SQL Server and then if connecting user is allowed access, run the needed JOIN query. Below are very simple examples of connection and querying. Do your research for appropriate connection parameters. Also, do remember an Oracle schema (just the user) is different from an SQL Server schema (namespace container of objects).

Oracle

A database link is a connection between two physical database servers that allows a client to access them as one logical database.

CREATE PUBLIC DATABASE LINK ... USING 'mssql_db';  -- TO BE RUN ONCE

SELECT o.Column1, s.Column1
FROM oracle_local_table o
INNER JOIN mssql_db.sql_server_remote_table s
  ON s.ID = o.ID

SQL Server

A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source.

EXEC master.dbo.sp_addlinkedserver           -- TO BE RUN ONCE
       @server = N'oracle_server', 
       @srvproduct=N'...', 
       @provider=N'...', 
       @provstr=N'...'

SELECT o.Column1, s.Column1
FROM oracle_server..user.oracle_remote_table o
INNER JOIN sql_server_local_table s
  ON s.ID = o.ID

-- ALTERNATIVELY
SELECT o.Column1, s.Column1
FROM OPENQUERY (oracle_server, 'SELECT * FROM scott.oracle_remote_table') o
INNER JOIN sql_server_local_table s
  ON s.ID = o.ID 

---

Alternatives in Python if above prove too difficult or requires high level privileged permission:

• Create a temp table in either side and dump remote table records via cursor.fetch and cursor.execute or cursor.executemany and then run JOIN query locally.

• Import both tables into an SQLite database (disk or in-memory instance) and run JOIN query. Note: in Python 3, sqlite3 is part of the standard library and hence ships with Python installations.

• Use Pythons's third party module, pandas, to import both tables into data frames and run merge (counterpart to SQL's JOIN). Interfacing with sqlalchemy, pandas can even dump data frames in one call to databases using to_sql.

Oracle Style Joins in SQL Server

Microsoft is using the ANSI ISO SQL Standard. Mark Rittman has a good explanation of ANSI joins and why you should use them. SQL Server, however, doesn't support the NATURAL JOIN syntax that's listed in that article and the ANSI standard. You may be more familiar with the old Oracle syntax, but it is the standard and something that you'll find on other database products.

To answer your question - there is no way to perform Oracle style joins on SQL Server. Even the *= and =* style joins have been deprecated and are being removed completely in the next version of the product.

Convert Oracle outer join to SQL Server

This should work in SQL Server:

SELECT CM.ModuleID,
       CM.ModuleDescription,
       CM.ImageIndex,
        CASE
         WHEN CMAC.ClassID IS NULL THEN
          'N'
         ELSE
          'Y'
       END AS Checked
  FROM APP_MODULES         CM,
       LEFT JOIN APP_PROFILE_CLASS CMAC 
            ON(CMAC.ProfileID = P_ProfileID AND CM.ModuleID = CMAC.ModuleID)
       LEFT JOIN APP_PROFILE CMAP
            ON(CMAP.ProfileID = CMAC.ProfileID)
  WHERE 1 = 1
   AND CM.ParentModuleID IS NULL
   AND CM.Activated = 'Y'
 ORDER BY CM.Ordem;

---

Related Topics