UNION with 2 DATABASES, SQL

Question

I need to integrate 2 TABLES from DBdifferent in one DGV.

I found several tutorials, but none that helped me from setting up to the connection, at least not the way I work with the connections.

Follow an ex. of how I call a Select:

MySqlConnection conexaoSLT = ClassConexao.DBEMG();

MySqlCommand _comando = new MySqlCommand(String.Format("SELECT * FROM tabel1 WHERE Carro = @tipoCarro"), conexaoSLT );

MySqlDataReader _reader = _comando.ExecuteReader();
_reader.Read();

....

How I can connect two Databases in a SELECT UNION ?

Answer 1

1. You should check if the user you are using on your connection has read permission in the other database.
2. In the SELECT shall use the name of the other bank before the table reference.

Example:

SELECT C.NOMCLI, C.CODCLI, E.CODCLI FROM DB1.CLIENTES C, DB2.CLIENTES E  

It may happen that in your databases it is necessary to put the user before the name of the database for example:

SELECT C.NOMCLI, C.CODCLI, E.CODCLI FROM USUARIO1.DB1.CLIENTES C, USUARIO 2.DB2.CLIENTES E  

The Rest to do the UNION, WHERE And ETC, you just reference normally.

Answer 2

As long as the user has permission in both banks.

Assuming it is SQL-Server and the tables are equal in both databases:

SELECT * FROM OUTRO_BANCO..TABELA WHERE ..... 
UNION
SELECT * FROM ESSE_BANCO..TABELA WHERE .....

If the tables are different:

SELECT campo1, campo2, campo3 FROM OUTRO_BANCO..TABELA WHERE ..... 
UNION
SELECT campo1, campo2, campo3 FROM ESSE_BANCO..TABELA WHERE .....

If it’s Oracle and equal tables:

SELECT * FROM OUTRO_BANCO.TABELA WHERE ..... 
UNION
SELECT * FROM ESSE_BANCO.TABELA WHERE .....

Oracle and different tables:

 SELECT campo1, campo2, campo3 FROM OUTRO_BANCO.TABELA WHERE ..... 
 UNION
 SELECT campo1, campo2, campo3 FROM ESSE_BANCO.TABELA WHERE .....