copy large amounts of data to MSSQL
Date: Thu, 5 Nov 2020 16:29:44 -0800
Message-ID: <CAKsxbLredc68L7=WY=j6pYkVxJ0xsOEN08sXavqLGNXOd76jYg_at_mail.gmail.com>
We have a new project where we need to copy a large dataset from Oracle (on prem) to SQL Server (cloud).
I found that just a
insert into table_at_mssql
select * from table;
Returns ORA-02025: all tables in the SQL statement must be at the remote database
I am trying out the SQL Server Gateway driver from Oracle because the documentation looks like it would work with the Remote Insert Rowsource https://docs.oracle.com/en/database/oracle/oracle-database/12.2/gmswn/database-gateway-for-sqlserver-features.html#GUID-513FBA3C-3458-4129-93E4-38DB2DF97F7A
However I get the same error. Does anyone know if this should work?
We've converted it to a PL/SQL loop inserting row by row but 5000 rows is taking 8 minutes. Oh and the funny thing is that this SQL Server instance is running on Oracle Cloud Infrastructure.
Any other ways you can think of to do this quick? 8 minutes is killer.
Thanks,
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Nov 06 2020 - 01:29:44 CET