万一有错误,还只怕会调换与导入文本同名的t1.bad文件。

4503.com 1

  • 字符编码
  • 字段分隔符
  • 行终止符
  • 日子或时间格式
  • 特殊字符
  • 导入字段的逐一
  • 导文件文件的表字段类型和尺寸是不是适当

 

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

 

  1. 动用SSMS的导出数据向导,使用Microsoft ODBC for Oracle或Oracle
    Provider for OLE DB连接受Oracle
  2. 导出到平面文件
  3. 导出包括数据的SQL脚本。
  4. 使用ETL工具。
  5. 协和开采软件。

figure-1

假定导出时还亟需做一些数据的管理,比方多表关联,字符处理等,比较复杂的逻辑,最佳是做成存款和储蓄进程,BCP间接调用存款和储蓄进程就能够。

 

[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

使用BCP并整合BULK
INSERT可实现大体积数据的飞速导出导入,并落实其自动化职业。对于一些些数码以来,操作也不算很复杂。那是除了SSMS上的图形化学工业具之外,又一个不行实用的工具。

私下认可下,生成的日记文件在当前目录下。无论成功与否,应当要翻开日志。看看是不是导入成功或停业,或是部分成功。导入的主题素材通常从日记文件就能够找到。

应用BULK
INSERT把数据导入到目的表数据。为巩固质量,可有时删除索引,导完之后再重新建立索引等。请小心要预先留下丰裕的磁盘空间。这里大约花了15分钟导完。

以下是日记文件,展现数据导入的有个别信息。成功导入了18495032行记录,未有导入失利的记录。

在Schedules页,配置实施的年月和频率等。达成。

以下使用第2种方法来开展数量迁移的。

 

从SQLServer导数据到Oracle大约有以下二种方法:

简易的导出例子1:

采取平面文件迁移数据,最大麻烦是便是特殊字符,或是有垃圾堆数据。假如原数据包罗与字符分隔符一样的字符,如这里面包车型客车“||”,或是有部分不可以知道的字符,如回车,换行符,等。这一个字符会变成导入时,分割字段错位,导致导入错误,数据导不全,以至导入失败。

3. 扩展

使用Oracle的SQL*LOADE讴歌ZDX导入平面文件。倘使Oracle中有曾经创办好的表,与导入文本对应。

采用方面介绍的用法导出多少:

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  
)

figure-9

使用SQL*LOADE福特Explorer注意多少个难点:

 

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;

code-3

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

4503.com,2.1 导出多少

但从导出导入的速度来讲,是最快的,平面文件可以跨分化的数据库进行搬迁。若是数额不容忍错过,只可以通过工具来导了,但速度会相对非常慢。

从个体来说,作者更爱好使用第两种跟queryout分选一同使用的写法,因为这么能够进一步灵活决定要导出的数额。借使实践BCP命令境遇这么的谬误提醒:

应用BCP合适导出大容积数据。这里导出千万品级的数目,也是便捷就会打响。

运用完之后,能够把sp_cmdshell关闭。

选用sqlldr命令把数量导入到Oracle中。

 

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

code-2

 

 

CREATE PROCEDURE sp_import_data
AS
BEGIN 
DECLARE @path NVARCHAR(500)
DECLARE @sql NVARCHAR(MAX)
/*S_PARAMETERS表是可以在应用程序上配置路径的*/
SELECT  @path = value_ + CONVERT(NVARCHAR, getdate(), 23) + '.txt' FROM S_PARAMETERS WHERE [type] = 'Import'
/*T4是一张临时的中间表。先把数据从文件中读入到中间表,最后通过脚本把T4中间表的数据插入到实际的业务表中*/
SET @sql=N'BULK INSERT T4 FROM '''+ @path + '''
WITH (
    FIELDTERMINATOR = ''*'',
    ROWTERMINATOR = ''\n''

)'
EXEC (@sql)
END
GO

figure-2

 

比较BCP的导入,BULK INSERT提供越来越灵敏的抉择。

在Steps页,在Command里推行写好的存款和储蓄进程。

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO

4503.com 2

此处不使用格式化文件进行导出导入的示范了。详细介绍与应用,请参谋联机丛书。

 

4503.com 3

在SSMS上同一时候也得以举行:

语法:

BULK INSERT dbo.T1 FROM 'E:\T1.txt'
WITH (
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n'    
)

介绍完BCP的导出导入,乃至BULK
INSERT的导入,下边实行部分实际的操作。为了好像实际条件,创设一张12个字段的表,包蕴有三种常用的数据类型,构造三千万的数码,富含汉语和乌克兰语。为了更加快插入测验数据,先不创制索引。在推行上面代码此前,请留神下数据库的日记恢复生机形式是还是不是设置为大体量情势或简捷情势,以至磁盘空间是或不是丰富(作者的实施中,数据变动后数据文件和日志文件大致须要40G的空中)。

 

figure-5

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

依照安全的思虑,系统默许未有拉开xp_cmdshell选项。使用上面语句开启此选项。

 

4503.com 4

4503.com 5

figure-17

 

 

BCP多少个常用的参数表明:

在General页,接纳Owner,这里选择sa。

 

figure-4

 

4. 总结

 

 

 

 

 

 

USE AdventureWorks2008R2
GO

IF OBJECT_ID(N'T1') IS NOT NULL
BEGIN
    DROP TABLE T1
END
GO

CREATE TABLE T1 (
    id_ INT,
    col_1 NVARCHAR(50),
    col_2 NVARCHAR(40),
    col_3 NVARCHAR(40),
    col_4 NVARCHAR(40),
    col_5 INT,
    col_6 FLOAT,
    col_7 DECIMAL(18,8),
    col_8 BIT,
    input_date DATETIME DEFAULT(GETDATE())
)
GO

WITH CTE1 AS ( 
SELECT a.[object_id] FROM master.sys.all_objects AS a,master.sys.all_objects AS b,sys.databases AS c
WHERE c.database_id <= 5
)

,CTE2 AS (
SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) as row_no FROM CTE1
)

INSERT INTO T1 (id_,col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8)
SELECT row_no,REPLICATE(N'博客园 ',10),NEWID(),NEWID(),NEWID(),CAST(row_no * RAND() * 10 AS INT),row_no * RAND(),row_no * RAND(),CAST(row_no * RAND() AS INT) % 2
FROM CTE2 WHERE row_no <= 20000000
GO

2.2 导入数据

 

4503.com 6

4503.com 7

 

bcp {[[database_name.][schema].]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [-mmax_errors] [-fformat_file] [-x] [-eerr_file]
    [-Ffirst_row] [-Llast_row] [-bbatch_size]
    [-ddatabase_name] [-n] [-c] [-N] [-w] [-V (70 | 80 | 90 )] 
    [-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term] 
    [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
    [-S [server_name[\instance_name]]] [-Ulogin_id] [-Ppassword]
    [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]

 

4503.com 8

4503.com 9

 

有关BULK
INSERT更详尽的辨证,参照他事他说加以考察: