Re: copy large amounts of data to MSSQL

From: <niall.litchfield_at_gmail.com>
Date: Sat, 7 Nov 2020 14:57:17 +0000
Message-ID: <CABe10sZ8Y+to5mLD3i81g3Aaxx5bM2-Gszb4eDeehvGr6uKQQw_at_mail.gmail.com>



Hi Jeff

I missed the on-prem to cloud part!

Your cloud provider probably has tooling that is designed for exactly this purpose. Microsoft provide
https://azure.microsoft.com/en-gb/services/database-migration , Amazon offer https://aws.amazon.com/dms/

On Fri, Nov 6, 2020 at 12:32 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
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Nov 07 2020 - 15:57:17 CET

Original text of this message