SQLServer导数据到Oracle

从SQLServer导数据到Oracle大概有以下几种方法：

使用SSMS的导出数据向导，使用Microsoft ODBC for Oracle或Oracle Provider for OLE DB连接到Oracle
导出到平面文件
导出包含数据的SQL脚本。
使用ETL工具。
自己开发软件。

以下使用第2种方法来进行数据迁移的。

使用BCP合适导出大容量数据。这里导出千万级别的数据，也是很快就能成功。

如果导出时还需要做一些数据的处理，比如多表关联，字符处理等，比较复杂的逻辑，最好是做成存储过程，BCP直接调用存储过程即可。

BCP "exec TestDB.dbo.export_t1 " queryout d:\export\t1.txt -c -t"||" -S"192.168.1.100" -Urpt -Prpt123
pause

USE TestDB
GO

CREATE PROC [dbo].[export_usercar]
AS
    SELECT  [carId]
           ,CONVERT(NVARCHAR(30), [addTime], 120)
           ,CONVERT(NVARCHAR(30), [lastSearchTime], 120)
           ,CONVERT(NVARCHAR(30), [updateTime], 120)
           ,[carType]
           ,[userTelephone]
           ,[isCorrect]
           ,[userId]
           ,[validFlag]
           ,[Channel]
           ,[carCode]
           ,[engineNumber]
           ,[carNumber]
    FROM    [TestDB].[dbo].[t1] WITH ( NOLOCK )
    WHERE   validFlag = 1
            AND isCorrect = 1;

把导出文件上传到Oracle所在的主机上，如CentOS下。

使用Oracle的SQL*LOADER导入平面文件。假如Oracle中有已经创建好的表，与导入文件对应。

把以下的内容用vi，写到import-t1.ctl

load data
CHARACTERSET 'ZHS16GBK'
infile '/data/import/t1.txt' "str '\r\n'"
into table SCOTT.T1
fields terminated by '||' TRAILING NULLCOLS
(
carId, 
addTime DATE "YYYY-MM-DD HH24:MI:SS",
lastSearchTime DATE "YYYY-MM-DD HH24:MI:SS",
updateTime DATE "YYYY-MM-DD HH24:MI:SS",
carType ,
userTelephone  ,
isCorrect  ,
userId  ,
validFlag ,
Channel ,
carCode  ,
engineNumber ,
carNumber  
)

使用SQL*LOADER注意几个问题：

字符编码
字段分隔符
行结束符
日期或时间格式
特殊字符
导入字段的顺序
导文件文件的表字段类型和长度是否合适

使用sqlldr命令把数据导入到Oracle中。

sqlldr user/"user_password" control=import-t1.ctl

默认下，生成的日志文件在当前目录下。无论成功与否，一定要查看日志。看看是否导入成功或失败，或是部分成功。导入的问题一般从日志文件即可找到。

如果有错误，还会生成与导入文件同名的t1.bad文件。

以下是日志文件，显示数据导入的一些信息。成功导入了18495032行记录，没有导入失败的记录。

[oracle@ttoracle /data/import]$ cat import-t1.log 

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jun 15 12:46:09 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Control File:   import-t1.ctl
Character Set ZHS16GBK specified for all input.

Data File:      /data/import/t1.txt
  File processing option string: "str '
'"
  Bad File:     t1.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table SCOTT.T1, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CARID                               FIRST     *           CHARACTER            
    Terminator string : '||'
ADDTIME                              NEXT     *           DATE YYYY-MM-DD HH24:MI:SS
    Terminator string : '||'
LASTSEARCHTIME                       NEXT     *           DATE YYYY-MM-DD HH24:MI:SS
    Terminator string : '||'
UPDATETIME                           NEXT     *           DATE YYYY-MM-DD HH24:MI:SS
    Terminator string : '||'
CARTYPE                              NEXT     *           CHARACTER            
    Terminator string : '||'
USERTELEPHONE                        NEXT     *           CHARACTER            
    Terminator string : '||'
ISCORRECT                            NEXT     *           CHARACTER            
    Terminator string : '||'
USERID                               NEXT     *           CHARACTER            
    Terminator string : '||'
VALIDFLAG                            NEXT     *           CHARACTER            
    Terminator string : '||'
CHANNEL                              NEXT     *           CHARACTER            
    Terminator string : '||'
CARCODE                              NEXT     *           CHARACTER            
    Terminator string : '||'
ENGINENUMBER                         NEXT     *           CHARACTER            
    Terminator string : '||'
CARNUMBER                            NEXT     *           CHARACTER            
    Terminator string : '||'


Table SCOTT.T1:
  18495032 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 214656 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:      18495032
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Fri Jun 15 12:46:09 2018
Run ended on Fri Jun 15 12:55:58 2018

Elapsed time was:     00:09:48.90
CPU time was:         00:03:37.62

使用平面文件迁移数据，最大麻烦是就是特殊字符，或是有垃圾数据。如果原数据包含与字符分隔符相同的字符，如这里面的“||”，或是有一些不可见的字符，如回车，换行符，等。这些字符会造成导入时，分割字段错位，导致导入错误，数据导不全，甚至导入失败。

但从导出导入的速度来说，是最快的，平面文件可以跨不同的数据库进行迁移。如果数据不容忍丢失，只能通过工具来导了，但速度会相对较慢。

SQLServer导数据到Oracle

相关推荐