Copy data between SQL and Oracle 1

[Andrzejek]

I have 2 places with the data: SQL Server and Oracle.
I can connect to both of them, Select the SQL Server data and insert record by record into Oracle. But this is slow, too many records.

Is there a way to do:
Insert Into MyOracleTable (Field1, Field2, Field3, ...)
Values (Select FieldA, FieldB, FieldC, ... from SQLServerTable)

 

[dhookom]

Can't you use a simple append query?

Insert Into MyOracleTable (Field1, Field2, Field3, ...)
Select FieldA, FieldB, FieldC, ... from SQLServerTable

 

[Andrzejek]

Yes, I can do this simplified version of the INSERT statement, but...
If I have:
Dim CnSQL As New ADODB.Connection
Dim CnOracle As New ADODB.Connection
...
CnOracle.Execute "Insert Into MyOracleTable ..."

How do I specify that part of my INSERT uses data from SQL Server?

 

[dhookom]

I thought both tables were linked into an Access front-end. When I have had multiple records from Excel to append to SQL Server, I created an SQL statement that use a union query in the from. My VBA looped through rows of Excel data creating a union query of

Into MyOracleTable (Field1, Field2, Field3, ...)
SELECT ValueA1, ValueB1, ValueC1, ...
UNION ALL
SELECT ValueA2, ValueB2, ValueC2

I found I could create about 50 unions to batch into SQL Server in one transaction.

 

[Andrzejek]

No, no Access linked tables.

The reason I've asked the question was - my code copying 26 000 records took about an hour. But that was over the Internet connection (I work from home with pretty fast connection). But... I also have a Virtual Machine (VM) at work, and I've tried the same code running on VM and it only took a couple of minutes.

So, I guess that's my answer to my issue.

 

[dhookom]

The “batch union“ has decreased the single stepping method drastically for me. It’s good to hear you have a workable solution.

 

[Andrzejek]

I will keep your “batch union“ idea on the back burner next time I need to do it.


Edit - Just tried UNION ALL approach in my INSERT statement (needed to add FROM DUAL since it is Oracle)
There was a tremendous increase in performance! Thanks Duane

 

[dhookom]

Thanks for the update. I provided technical support for about 30 factories in North America. Each factory had to submit annual production targets for each product. I distributed Excel files that pulled in the previous results so they could plan for the next year. After updating possibly thousands of records, they could click a button to batch upload into normalized SQL Server tables. I did some testing of batch sizes and settled around 100 records.

 

[remeng]

Hi Andy,

You might want to look into a Power Query solution for this instead of VBA. Over the last few months I've noticed that a lot of what we code can be done a lot faster with Power Query. Also, with that system you can connect to the source data directly.

Another benefit is that you can process over the max number of lines in Excel.

Just an idea

Mike