Re: copy large amounts of data to MSSQL

Hello,

Have you tried doing a pull from the MS-SQL-Server side? How much data in the table?
How "far" apart are the Oracle and MS sides? How you tried sending one row and timing that? Look at the MS-SQL-Server funtion OPENQUERY.

Regards,
Frank

On Fri, Nov 6, 2020 at 12:31 AM Jeff Chirco <backseatdba_at_gmail.com> wrote:

> 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,
> Jeff
>

-- 
+353-86-0695383

--
http://www.freelists.org/webpage/oracle-l