SQL Server 2012 Big Data import solution for Oracle

In practice, the tables in SQL Server need to be imported into Oracle. It is no problem to have previously tried DTS directly with SQL Server. But this time because of the amount of data in tens, so reported insufficient virtual memory. Finally, the solution is to export the TXT file and the Oracle SQLLDR import through SQL Server.

One. SQL Server export TXT file

Method 1. Use DTS for export.

Method 2: Use the bcp command to export, to see in-park articles on the use of BCP commands in SQL Server and Data Bulk Import Export

TXT file can be generated by the above method

Two. Oracle sqlldr import TXT file

1. Create Sqlldr. CTL file, specific parameters command to view related articles: Importing text files with Oracle SQLLDR TXT summarizing Oracle SQLLDR importing data using default values and ways to improve performance

OPTIONS (skip_index_maintenance=true,direct=true, bindsize=20971520, readsize=20971520, errors=-1, rows=500000)--unrecoverableload Data--CHARACTERSET al32utf8load DATA INFILE'C:\xxxxxe.txt'---------data file, or TXT file Append into TABLE demo7.tms_branchcode2-----table name, full path fields TERMINATED by X' the'-----the data is separated by tab TRAILING Nullcols (ID,------table field branch_plant, So_number,trip_number,shippment_date--"to_date (: Shippment_date, "Yyyy-mm-dd hh24:mi:ss")"Sold_to,sold_to_name,ship_to,ship_to_name,barcode_info,barcode_seg_1,barcode_seg_2,barcode_seg_3,barcode_ Seg_4,barcode_seg_5, Last_updated_time--"to_date (: Shippment_date, "Yyyy-mm-dd hh24:mi:ss")" )

2. Run the. ctl file to import data, open CMD to run the following file

C:/>sqlldr Userid=system/[email protected] ServiceName Control=c:\xxx.ctl

SQL Server 2012 Big Data import solution for Oracle