Run SQL select query in Netezza and Insert the data in Oracle Table

[ARoyT]

Hi, I am trying to get VBA to run a select query in a netezza database and then insert that data into a table in an oracle database table, but have failed so far. Can anyone help fill in the blanks / show me what I'm missing or doing wrong please?

Sub Midas_to_Oracle_table()
    
    Dim con As ADODB.Connection
    Dim ConnectionString As String
    Dim recset As New ADODB.Recordset
    
    Dim mcon As ADODB.Connection
    Dim mConnectionString As String
    Dim mrecset As New ADODB.Recordset
    
    Dim SqlQry As String
    Dim mSqlQry As String
        
    Set con = New ADODB.Connection
    Set recset = New ADODB.Recordset
    
    Set mcon = New ADODB.Connection
    Set mrecset = New ADODB.Recordset
    
    mConnectionString = "dsn=NZSQL;servername=servername;port=1234;database=database;User ID=me01;password=password123"
    ConnectionString = "GOODSQL.1;User ID=cheese_data;password=password456;Data Source=ORACLE"
    
    mcon.Open mConnectionString
    
    mSqlQry = "SELECT COLUMNS FROM TABLE WHERE ETC "
    
    mrecset.Open mSqlQry, mcon
     
    con.Open ConnectionString
        
    ''''SqlQry = " INSERT INTO MY_ORACLE_TABLE (SELECT * FROM   " & [mrecset] & " ) "
    SqlQry = " INSERT INTO MY_ORACLE_TABLE (SELECT * FROM [mrecset]  ) "

    recset.Open SqlQry, con
    
    recset.Close
    
    mcon.Close
    Set mrecset = Nothing 

    con.Close
    Set recset = Nothing  
    
    'MsgBox "I went to a Pretenders gig once.  It was a tribute act"
    
    End Sub

The connection strings work. It gets down as far as

    recset.Open SqlQry, con

then there's a message saying "invalid table name".

I know MY_ORACLE_TABLE is ok because I can replace the sql

" INSERT INTO MY_ORACLE_TABLE (SELECT * FROM   " & [mrecset] & " ) "

with

" select * from MY_ORACLE_TABLE  "

and it will run that, so assume the problem lies with

SELECT * FROM   " & [mrecset] & " ) "

Thanks

 

[ARoyT]

It appears it can't be done like this with VBA. I'm going to export the netezza dataset (copyfromrecordset) to excel and then use a sql loader to import the data to the oracle table. I know how to do this already but was hoping for a slicker and quicker (one click) solution. I did manage to export the data to excel and use a loop to import the data into the oracle table but it was taking far too long to got through the 70k+ rows so will revert to the sql loader.