Odbc & Sql connection in one query

Question

I wanna select some records from the informix database via Odbc connection and insert them to a Sql database table.

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

I've searched regrading that, but they didn't solve my issue. Is it possible to have both connections at one place?
Any help or suggestions would be appreciated. Thanks

Answer 1

I believe that an ODBC connection is made using an ODBC driver custom-made for the specific DB engine (e.g. Oracle, MSSQL, PSQL, etc.), and hence, a sole query cannot contain two different database engines as the query runs through a specific driver through the ODBC interface.

However, you can easily utilize two ODBC drivers in on code using a simple script in any programming language which has an ODBC library. For example, I use Python along with pyodbc to initialize multiple connections and transfer data between MSSQL, MySQL and PSQL databases. Here's a pseudo-code example:

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 realize I'm taking you in a different direction, but hopefully this is still helpful in someway. Happy to provide other examples if needed.