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

ARoyT

New Member
Joined
May 18, 2013
Messages
7
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?

VBA Code:
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
VBA Code:
    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
SQL:
" INSERT INTO MY_ORACLE_TABLE (SELECT * FROM   " & [mrecset] & " ) "
with
SQL:
" select * from MY_ORACLE_TABLE  "
and it will run that, so assume the problem lies with
SQL:
SELECT * FROM   " & [mrecset] & " ) "

Thanks
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top