SQLServer导数据到Oracle
程序员文章站
2022-07-05 17:12:58
从SQLServer导数据到Oracle大概有以下几种方法: 1. 使用SSMS的导出数据向导,使用Microsoft ODBC for Oracle或Oracle Provider for OLE DB连接到Oracle 2. 导出到平面文件 3. 导出包含数据的SQL脚本。 4. 使用ETL工具 ......
从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
使用平面文件迁移数据,最大麻烦是就是特殊字符,或是有垃圾数据。如果原数据包含与字符分隔符相同的字符,如这里面的“||”,或是有一些不可见的字符,如回车,换行符,等。这些字符会造成导入时,分割字段错位,导致导入错误,数据导不全,甚至导入失败。
但从导出导入的速度来说,是最快的,平面文件可以跨不同的数据库进行迁移。如果数据不容忍丢失,只能通过工具来导了,但速度会相对较慢。
上一篇: 数据库事物4个特性
下一篇: T-SQL:CTE用法(十)
推荐阅读
-
PowerDesigner 建立与SQLSERVER 2005数据库的连接以便生成数据库和从数据库生成到PD中
-
sql server 2005如何导出数据到oracle 11g
-
PowerDesigner 建立与数据库的连接以便生成数据库和从数据库生成到PD中(Oracle 10G版)
-
PowerDesigner 建立与SQLSERVER 2005数据库的连接以便生成数据库和从数据库生成到PD中
-
PowerDesigner 建立与数据库的连接以便生成数据库和从数据库生成到PD中(Oracle 10G版)
-
Oracle数据库从入门到精通 多表查询知识以及范例
-
oracle,mysql,SqlServer三种数据库的分页查询的实例
-
SQLServer导出数据到MySQL实例介绍
-
sqlserver导入数据到mysql的详细图解
-
通过Java实现批量导入数据到SQLServer数据库