search

JuliaDatabases / ODBC.jl

An ODBC interface for the Julia programming language
https://odbc.juliadatabases.org/stable
Other
106 stars 63 forks source link

How to execute query with multiple DSNs? #324

Closed JockLawrie closed 3 years ago

JockLawrie commented 3 years ago

Hi there,

I am iteratively building a table in a SQL Server DSN called Output. My approach has been to create an empty table in Output with 2 columns - ID, outcome. Then each iteration appends rows to this table. The data for each iteration is pulled from a 2nd DSN, let's call it Input, which is a database on the same SQL Server instance.

Ideally I could do a SELECT INTO or similar so that this data movement happens on the server. But I can't seem to get it working. How do I this using ODBC.jl?

Cheers, Jock

JockLawrie commented 3 years ago

Solved. The solution lies in the SQL code, not the ODBC.jl code. One can query a DSN using a connection to a different DSN. For reference:

q = "SELECT * FROM dsn1.dbo.mytable"  # Query dsn1
conn = ODBC.Connection(dsn2)          # Connect to dsn2
data = DataFrame(DBInterface.execute(conn, q))  # Execute the query of dsn1 using the connection to dsn2
DBInterface.close!(conn)