Odbc & Sql connection in one query

Question

I want to select some records from the informix database through an Odbc connection and insert them into the Sql database table.

INSERT INTO SAS.dbo.disconnectiontemp (meterno) SELECT DISTINCT met_number FROM Bills.dbadmin.MeterData 

I searched for this, but they did not solve my problem. Is it possible to have both connections in one place?
Any help or suggestions would be appreciated. Thanks

Answer 1

I believe that an ODBC connection is made using an ODBC driver configured for a specific database engine (for example, Oracle, MSSQL, PSQL, etc.), and therefore a single query cannot contain two different database mechanisms as a query passes through a specific driver through the ODBC interface.

However, you can easily use two ODBC drivers in your code using a simple script in any programming language that has an ODBC library. For example, I use Python with pyodbc to initialize multiple connections and transfer data between MSSQL, MySQL, and PSQL databases. Here is an example of pseudo code:

 import pyodbc psql_cursor = pyodbc.connect('<PSQL_ODBC_CONNECTION_STRING>').cursor() mysql_cursor = pyodbc.connect('<MYSQL_ODBC_CONNECTION_STRING>').cursor() result_set = mysql_cursor.execute('<SOME_QUERY>').fetchall() to_insert = <.... Some code to transform the returned data if needed ....> psql_cursor = psql_cursor.execute('insert into <some_table> VALUES (%s)' % to_insert) 

I understand that I’m taking you in a different direction, but I hope this helps anyway. It is nice to provide other examples if necessary.