图片 17

BCP导出导入大容量数据实践,SQLServer导数据到Oracle

从SQLServer导数据到Oracle大致有以下两种方法:

前言

外界表概述

表面表只好在Oracle
9i之后来选用。简单地说,外界表,是指一纸空文于数据库中的表。通过向Oracle提供描述外界表的元数据,大家能够把三个操作系统文件正是一个只读的数据库表,如同这一个数据存款和储蓄在二个家常数据库表中同样来开展访谈。外界表是对数据库表的延伸。

  1. 应用SSMS的导出数据向导,使用Microsoft ODBC for Oracle或Oracle
    Provider for OLE DB连接收Oracle
  2. 导出到平面文件
  3. 导出包涵数据的SQL脚本。
  4. 使用ETL工具。
  5. 温馨开荒软件。

SQL
SEEnclaveVELAND提供各种差别的多寡导出导入的工具,也得以编写制定SQL脚本,使用存款和储蓄进度,生成所需的数据文件,以致足以扭转满含SQL语句和多少的台本文件。各有利害,以适用差异的必要。上边介绍大容积数据导出导入的利器——BCP实用工具。同一时候在后面也介绍BULK
INSERT导入大容积数据,甚至BCP结合BULK
INSERT做多少接口的实践(在SQL二〇〇八ENVISION2上推行卡塔尔。

外表表的风味 

位于文件系统之中,按自然格式分割,如文本文件只怕其余类其他表可以当作外界表。
对外界表的走访能够通过SQL语句来成功,而无需先将表面表中的数量装载进数据库中。
表面数据表都以只读的,因而在表面表无法执行DML操作,也不能够创制索引。
ANALYZE语句不支持收集外界表的总括数据,应该运用DMBS_STATS包来访问外界表的总括数据。

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

 

创办外界表的引人瞩目事项 

应用BCP合适导出大体积数据。这里导出千万等级的数量,也是全速就会成功。

1. BCP的用法

1.内需先制造目录对象

在制造指标的时候,需求当心,Oracle数据库系统不会去断定这一个目录是还是不是确实存在。假使在输入那么些目录对象的时候,十分大心把门路写错了,那恐怕那些外部表仍旧能够符合规律创设,不过却心有余而力不足查询到数码。由于创立目录对象时,缺少这种自个儿反省的建制,为此在将路线付与给那几个目录对象时,必要极度的专心。别的要求注意的是路径的大大小小写。在Windows操作系统中,其路线是不区分朗朗上口写的。而在Linux操作系统,这几个门路须要区分抑扬顿挫写。故在分化的操作系统
中,建构目录对象时索要专一这些尺寸写的差距

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

BCP 实用工具能够在 Microsoft SQL Server
实例和客商钦命格式的数据文件间大容积复制数据。使用
BCP实用工具可以将多量新行导入 SQL Server
表,或将表数据导入数据文件。除非与 queryout
选项一同行使,否则使用该实用工具无需理解 Transact-SQL
知识。BCP不仅可以够在CMD提示符下运营,也足以在SSMS下实践。

2.对于操作系统文件的渴求

建设构造外界表时,必需钦命操作系统文件所运用的相间符号。况且该分隔符有且唯有一个。成立外界表时,不可能含有标题列。假设那些标题音讯与表面表的字段类型不相仿(如字段内容是number数据类型,而标题新闻则是字符型数据,则在询问时就会出错卡塔 尔(阿拉伯语:قطر‎。假诺数据类型刚好生龙活虎致的话,这么些标题消息Oracle数据库也会作为普通记录来比较。

当Oracle数据库系统访谈这一个操作系统文件的时候,会在此个文件所在的目录自动创立二个日记文件。无论最终是还是不是访谈成功,那些日志文件都会按时创立。查看这一个日志文件,能够了解数据库访问外界表的频率、是不是中标访问等等。私下认可情形下,该日志在与外表表的如出意气风发辙directory下发出。

BCP "exec TestDB.dbo.export_t1 " queryout d:exportt1.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;

图片 1

3.在创建临时表时的相关限量

对表中字段的名号存在特殊字符的状态下,必需使用德文状态的下的双引号将该表列名称连接起来。如运用”SalseID#”。
对此列名字中特殊符号未选拔双引号括起来时,会促成不可能符合规律查询数据。
建议不用选取特殊的列标题字符
在创立外界表的时候,并不曾在数据库中创立表,也不会为外界表分配任何的积攒空间。
创建外界表只是在多少字典中成立了表面表的元数据,以便对应访谈外部表中的数据,而不在数据库中存放外界表的数量。
简言之地说,数据仓库储存款和储蓄的只是与外表文件的意气风发种对应提到,如字段与字段的附和关系。而从未存款和储蓄实际的数据。
由于存款和储蓄实际多少,故不可能为外部表创造索引,同期在数额运用DML时也不协助对外界表的插入、更新、删除等操作。

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

figure-1

4.去除却界表或然目录对象

相近情况下,先删除此之外界表,然后再删除目录对象,纵然目录对象中有三个表,应除去全数表之后再删除目录对象。
假定在未删减外界表的情况下,强制删除了目录,在查询到被剔除的外表表时,将抽出”对象不设有”的错误音信。
查询dba_external_locations来获得当前具有的目录对象以至有关的外表表,同时会付给那么些外界表所对应的操作系统文件的名字。 假设只是在数据库层面上剔除此而外界表,并不会活动删除操作系统上的外表表文件。

使用Oracle的SQL*LOADELAND导入平面文件。假诺Oracle中有已经创建好的表,与导入文本对应。

 

 5.对于操作系统平台的限定

分裂的操作系统对于外界表有两样的表达和展现方式
如在Linux操作系统中开创的文本是分号分隔且每行一条记下,但该公文在Windows操作系统上开辟则并不是那样。
指出幸免差别操作系统甚至分裂字符集所拉动的震慑

把以下的剧情用vi,写到import-t1.ctl

语法:

创办外界表 

选取CREATE TABLE语句的O中华VGANIZATION
EXTENERAL子句来创建国门外界表。外界表不分红任何盘区,因为独有是在数额字典中创设元数据。

load data
CHARACTERSET 'ZHS16GBK'
infile '/data/import/t1.txt' "str 'rn'"
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  
)
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]"]

 

1.外界表的创导语法

createtabletable_name
           (col1 datatype1,col2 datatype2,col3 datatype3)
            organization exteneral
           (…..)
详尽语法可参见笔者的另两篇小说

Oracle外部表ORACLE_DATAPUMP类型的创办语法精解:

Oracle外部表ORACLE_LOADE大切诺基类型的创造语法详细解释:

使用SQL*LOADEENCORE注意多少个难点:

 

2.由询问结果集,使用Oracle_datapump来填充数据来变化外界表

  • 字符编码
  • 字段分隔符
  • 行终止符
  • 日期或时刻格式
  • 特殊字符
  • 导入字段的逐生龙活虎
  • 导文件文件的表字段类型和尺寸是否确切

简单的说的导出例子1:

a.成立系统目录以至Oracle数据目录名来创立对应涉及,同不平日间给与权限

$ mkdir -p /home/oracle/external_tb/data

create or replace directory data_dir as '/home/oracle/external_tb/data/';
grant read,write on directory data_dir to scott;

选拔sqlldr命令把多少导入到Oracle中。

图片 2

b.创制外界表

create table ex_tb1
            (ename,job,sal,dname)
            organization external
            (type oracle_datapump default directory data_dir location('ex_tb1'))
            parallel 1
            as select ename,job,sal,dname from emp join dept on emp.deptno=dept.deptno;
sqlldr user/"user_password" control=import-t1.ctl

figure-2

c.验证外界表

select * from ex_tb1;

ENAME                       JOB           SAL  DNAME
------------------------- -------------------- ---- -------------------------
CLARK                  MANAGER              2450 ACCOUNTING
KING                     PRESIDENT             5000 ACCOUNTING
MILLER                   CLERK                 1300 ACCOUNTING
JONES                    MANAGER               2975 RESEARCH
FORD                     ANALYST               3000 RESEARCH
ADAMS                    CLERK                 1100 RESEARCH
SMITH                    CLERK                  800 RESEARCH
SCOTT                    ANALYST               3000 RESEARCH
WARD                     SALESMAN              1250 SALES
TURNER                   SALESMAN              1500 SALES
ALLEN                    SALESMAN              1600 SALES
JAMES                    CLERK                  950 SALES
BLAKE                    MANAGER               2850 SALES
MARTIN                   SALESMAN              1250 SALES

14 rows selected.

对此使用上述措施开创的外界表能够将其复制到其余路径作为外界表的庐山真面目目数据来生成新的外表表,用于转移数据。

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

 

d.将表面表文件复制二个新的文书名,用以模拟到别的服务器上

$ cp /home/oracle/external_tb/data/ex_tb1 /home/oracle/external_tb/data/in_tb1

假设有荒诞,还可能会变卦与导入文本同名的t1.bad文件。

粗略的导出例子2:

e. 新建表,将上述外界表的数量导入到新表中

create table in_tb1
            (ename varchar2(10),job varchar2(9),sal number(7,2),dname varchar(14))
            organization external
            (type oracle_datapump default directory data_dir location('in_tb1'));

以下是日记文件,彰显数据导入的有个别新闻。成功导入了18495032行记录,未有导入退步的笔录。

图片 3

f.验证新外界表的数量

select * from in_tb1;

ENAME                       JOB           SAL  DNAME
------------------------- -------------------- ---- -------------------------
CLARK                  MANAGER              2450 ACCOUNTING
KING                     PRESIDENT             5000 ACCOUNTING
MILLER                   CLERK                 1300 ACCOUNTING
JONES                    MANAGER               2975 RESEARCH
FORD                     ANALYST               3000 RESEARCH
ADAMS                    CLERK                 1100 RESEARCH
SMITH                    CLERK                  800 RESEARCH
SCOTT                    ANALYST               3000 RESEARCH
WARD                     SALESMAN              1250 SALES
TURNER                   SALESMAN              1500 SALES
ALLEN                    SALESMAN              1600 SALES
JAMES                    CLERK                  950 SALES
BLAKE                    MANAGER               2850 SALES
MARTIN                   SALESMAN              1250 SALES

14 rows selected.
[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

figure-3

g.创立平常的表,将表面表数据导入,那便是运用ORACLE_DATAPUMP类型的额外界表完毕多少迁移

create table tb1 as select * from in_tb1;

应用平面文件迁移数据,最大麻烦是正是特殊字符,或是有垃圾堆数据。假设原数据包括与字符分隔符相仿的字符,如这中间的“||”,或是有部分不可知的字符,如回车,换行符,等。那几个字符会变成导入时,分割字段错位,引致导入错误,数据导不全,以致导入失利。

 

3.运用外界文件数量,使用oracle_loader来填充数据来扭转外部表

但从导出导入的进程来讲,是最快的,平面文件可以跨区别的数据库举办搬迁。借使数额不容忍错过,只好通过工具来导了,但速度会相对一点也不快。

在SSMS上同期也能够实践:

 a.筹算外界数据源文件

cat /home/oracle/external_tb/data/1.txt
"7369","SMITH","CLERK","7902","17-DEC-80","100","0","20"
"7499","ALLEN","SALESMAN","7698","20-FEB-81","250","0","30"
"7521","WARD","SALESMAN","7698","22-FEB-81","450","0","30"
"7566","JONES","MANAGER","7839","02-APR-81","1150","0","20"

$ cat /home/oracle/external_tb/data/2.txt
"7654","MARTIN","SALESMAN","7698","28-SEP-81","1250","0","30"
"7698","BLAKE","MANAGER","7839","01-MAY-81","1550","0","30"
"7934","MILLER","CLERK","7782","23-JAN-82","3500","0","10"
EXEC [master]..xp_cmdshell
'BCP TestDB_2005.dbo.T1 out E:T1_02.txt -c -T'
GO

b.创设外界表

create table emp_new(
                    emp_id number(4),
                    ename varchar2(15),
                    job varchar2(12),
                    mgr_id number(4),
                    hiredate date,
                    salary number(8),
                    comm number(8),
                    dept_id number(2)
                    )
            organization external
                    (
                    type oracle_loader
                    default directory data_dir
                    access parameters(
                                    records delimited by newline
                                    badfile 'emp_new%a_%p.bad'
                                    logfile 'emp_new%a_%p.log'
                                    fields terminated by ','
                                    optionally enclosed by '"'
                                    lrtrim missing field values are null
                                    reject rows with all null fields
                                    )
                    location ('1.txt','2.txt')
)
parallel 
reject limit unlimited;

code-1

c.验证外界表

select * from emp_new;

EMP_ID ENAME      JOB              MGR_ID    HIREDATE            SALARY     COMM       DEPT_ID
------ ---------- --------------- ---------- ------------------- ---------- ---------- ----------
  7654 MARTIN     SALESMAN        7698       1981-09-28 00:00:00 1250       0           30
  7698 BLAKE      MANAGER         7839       1981-05-01 00:00:00 1550       0           30
  7934 MILLER     CLERK           7782       1982-01-23 00:00:00 3500       0           10
  7369 SMITH      CLERK           7902       1980-12-17 00:00:00 100        0           20
  7499 ALLEN      SALESMAN        7698       1981-02-20 00:00:00 250        0           30
  7521 WARD       SALESMAN        7698       1981-02-22 00:00:00 450        0           30
  7566 JONES      MANAGER         7839       1981-04-02 00:00:00 1150       0           20

7 rows selected.

 

 4.外表表相关视图

图片 4

a.查看表面表音信

select TABLE_NAME,TYPE_NAME,DEFAULT_DIRECTORY_NAME,REJECT_LIMIT,ACCESS_PARAMETERS from user_external_tables;

 

figure-4

b.得到平面文件的地点

select * from user_external_locations order by table_name;

TABLE_NAME LOCATION   DIRECTORY DIRECTORY_NAME
---------- ---------- --------- --------------------
EMP_NEW    1.txt      SYS       DATA_DIR
EMP_NEW    2.txt      SYS       DATA_DIR
EX_TB1     ex_tb1     SYS       DATA_DIR
IN_TB1     in_tb1     SYS       DATA_DIR

 

 

外表表定义的多少个重大 

 

1.OPRADOGANIZATION EXTEQashqaiNAL第一字,一定要有。以标记定义的表为外界表。

EXEC [master]..xp_cmdshell
'BCP "SELECT * FROM TestDB_2005.dbo.T1" queryout E:T1_03.txt -c -T'
GO

2..根本参数外部表的品类

ORACLE_LOADERubicon:定义外界表的缺省方式,只好只读情势完结公文数据的装载。
ORACLE_DATAPUMP:援救对数据的装载与卸载,数据文件必得为二进制dump文件。可以从表面表提取数据装载到里面表,也能够从里边表卸载数据作为二进制文件填充到外界表。

code-2

3.DEFAULT DIRECTOPAJEROY:缺省的目录指明了表面文件所在的门道

 

 

4.LOCATION:定义了外界表的职分

图片 5

5.ACCESS PARAMETEOdysseyS:描述怎么样对外表表展开访谈

RECO福睿斯DS关键字后定义怎样辨别数据行  
DELIMITED BY
‘XXX’——换行符,常用newline定义换行,并指明字符集。对于特别的字符则必要独自定义,如特殊符号,能够使用OX’十六个人值’,举个例子tab(/t)的16个人是9,则DELIMITEDBY0X’09’;
cr(/r)的13个人是d,那么正是DELIMITEDBY0X’0D’。
SKIP X ——跳过X行数据,有个别公文中首先行是列名,须要跳过第意气风发行,则选拔SKIP
1。
FIELDS关键字后定义怎么样鉴定分别字段,常用的如下:
FIELDS:TERMINATED BY ‘x’——字段分割符。
ENCLOSED BY ‘x’——字段引用符,饱含在这里标志内的数据都不失为二个字段。
诸如生机勃勃行数据格式如:”abc”,”a””b,””c,”。使用参数TERMINATED BY ‘,’
ENCLOSED BY
‘”‘后,系统会读到五个字段,第三个字段的值是abc,第4个字段值是a”b,”c,。
LRT奥德赛IM ——删除首尾空白字符。
MISSING FIELD VALUES ARE NULL——有个别字段空缺值都设为NULL。
对于字段长度和分割符不鲜明且计划作为外界表文件,能够使用UltraEdit、艾德itplus等来进展解析测量检验,若是文件超大,则须要思考将文件分割成小文件并从当中提取数额开展测量试验。

figure-5

表面表对错误的拍卖 

REJECT LIMIT UNLIMITED
在创造外界表时最终参与LIMIT子句,表示能够允许错误的发出个数。默许值为零。设定为UNLIMITED则错误不受限定
BADFILE和NOBADFILE子句
用于钦定将捕获到的转移错误寄放到哪个文件。假设钦定了NOBADFILE则象征忽视转变时期的错误
生龙活虎旦未钦点该参数,则系统活动在源目录下转移与外表表同名的.BAD文件BADFILE记录这一次操作的结果,后一次将会被遮住
LOGFILE和NOLOGFILE子句
同样在access parameters中加入LOGFILE
‘LOG_FILE.log’子句,则持有Oracle的错误音信放入’LOG_FILE.log’中
而NOLOGFILE子句则表示不记录错误音讯到log中,如忽视该子句,系统活动在源目录下转移与外表表同名的.LOG文件
留意以下多少个科学普及的难点
1.外表表平时境遇BUFFE昂科雷不足的情况,由此尽或者的增大READSIZE
2.换行符不对发生的主题材料。在分裂的操作系统中换行符的象征方法不同,遇到错误日志提醒如是换行符难题,能够运用
UltraEdit张开,直接看十五进制
3.特定行报错开上下班时间,查看带有”BAD”的日记文件,在那之中保存了失误的数量,用记事本张开看看这里出错,是不是留存于外界表定义相冲突

 

外界表的局限性 

1.SQLLD奥德赛方可内定多少提交一遍,即ROWS=?,
外界表却未有,那对于大数据量的导入某些不方例。
2.sqlldr errors意味同意错误的行数,外界表用REJECT LIMIT
UNLIMITED,那一个意义上基本肖似。
3.外界表的列不可能钦命为not nullable,那样就很难谢绝某列为空值的笔录。
4.表面表不能使用continueif ,假如记录有换行的就比较难管理。

 

从个人来说,我更赏识使用第两种跟queryout选用一同行使的写法,因为那样能够更灵敏决定要导出的数码。如果实施BCP命令遭遇那样的乖谬提醒:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see "Surface Area Configuration" in SQL Server Books Online.

依附安全的设想,系统暗许未有开启xp_cmdshell选项。使用上面语句开启此选项。

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

code-3

 

选拔完事后,能够把sp_cmdshell关闭。

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO

code-4

 

BCP导入数据

改进figure-第22中学的out为in就可以,把数量导入。

图片 6

figure-6

 

图片 7

figure-7

 

利用BULK INSERT导入数据

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

code-5

 

图片 8

figure-8

 

有关BULK
INSERT更详实的辨证,参谋:

相比BCP的导入,BULK INSERT提供更加灵活的采纳。

 

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

database_name 指定的表或视图所在数据库的名称。如果未指定,则使用用户的默认数据库。
in | out| queryout | format
  • in 从文件复制到数据库表或视图。

  • out 从数据库表或视图复制到文件。如果指定了现有文件,则该文件将被覆盖。提取数据时,请注意 bcp 实用工具将空字符串表示为 null,而将 null 字符串表示为空字符串。

  • queryout 从查询中复制,仅当从查询大容量复制数据时才必须指定此选项。

  • format 根据指定的选项(-n-c-w-N)以及表或视图的分隔符创建格式化文件。大容量复制数据时,bcp 命令可以引用一个格式化文件,从而避免以交互方式重复输入格式信息。format 选项要求指定 -f 选项;创建 XML 格式化文件时还需要指定 -x 选项。

    in 从文件复制到数据库表或视图。
    out 从数据库表或视图复制到文件。如果指定了现有文件,则该文件将被覆盖。提取数据时,请注意 bcp 实用工具将空字符串表示为 null,而将 null 字符串表示为空字符串。
    queryout 从查询中复制,仅当从查询大容量复制数据时才必须指定此选项。

-c 使用字符数据类型执行该操作。此选项不提示输入每个字段;它使用 char 作为存储类型,不带前缀;使用 t(制表符)作为字段分隔符,使用 rn(换行符)作为行终止符。
-w 使用 Unicode 字符执行大容量复制操作。此选项不提示输入每个字段;它使用 nchar 作为存储类型,不带前缀;使用 t(制表符)作为字段分隔符,使用 n(换行符)作为行终止符。
-tfield_term 指定字段终止符。默认值为 t(制表符)。使用此参数可以替代默认字段终止符。
-rrow_term 指定行终止符。默认值为 n(换行符)。使用此参数可替代默认行终止符。
-Sserver_name[ instance_name] 指定要连接的 SQL Server 实例。如果未指定服务器,则 bcp 实用工具将连接到本地计算机上的默认 SQL Server 实例。如果从网络或本地命名实例上的远程计算机中运行 bcp 命令,则必须使用此选项。若要连接到服务器上的 SQL Server 默认实例,请仅指定 server_name。若要连接到 SQL Server 的命名实例,请指定 server_nameinstance_name。
-Ulogin_id 指定用于连接到 SQL Server 的登录 ID。
-Ppassword 指定登录 ID 的密码。如果未使用此选项,bcp 命令将提示输入密码。如果在命令提示符的末尾使用此选项,但不提供密码,则 bcp 将使用默认密码 (NULL)。
-T 指定 bcp 实用工具通过使用集成安全性的可信连接连接到 SQL Server。不需要网络用户的安全凭据、login_id 和 password。如果未指定 –T,则需要指定 –U–P 才能成功登录。

更详尽的参数,请参照他事他说加以考查:

 

 

 

2. 实践

2.1 导出多少

介绍完BCP的导出导入,以致BULK
INSERT的导入,上边举香港行政局地事实上的操作。为了好像实际条件,创造一张11个字段的表,包蕴有两种常用的数据类型,构造2002万的数据,包蕴普通话和保加卡托维兹语。为了更加快插入测验数据,先不创制索引。在推行上边代码早前,请留意下数据库的日志恢复情势是还是不是设置为大体量情势或简捷情势,以至磁盘空间是不是丰盛(作者的实行中,数据变化后数据文件和日志文件大致要求40G的长空卡塔 尔(阿拉伯语:قطر‎。

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

code-6

 

进程要花上几分钟的年月工夫产生,请恒心等待一下。关于数据的布局,可以参见笔者的另大器晚成篇博文:

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

EXEC [master]..xp_cmdshell
'BCP AdventureWorks2008R2.dbo.T1 out E:T1_04.txt -w -T -S KENSQLSERVER08R2'
GO

code-7

 

此地运用-w参数。BCP能够在CMD下导出多少,测量检验导出2002万条记下,作者的记录簿使用了近8分钟左右的时间。BCP同有时间也得以在SSMS中实行,使用了6分多钟时间,比CMD下速度要快些,生成的文件大小后生可畏致,每一种文件近5GB。

图片 9

figure-9

 

图片 10

figure-10

 

而对于复杂的大容积导入意况,日常都会须要格式化文件。在以下意况下,必需接受格式化文件:

  • 持有不一致架构的四个表使用同样数据文件作为数据源。

  • 数据文件中的字段数差别于指标表中的列数;举例:

    • 对象表中起码含有贰个定义了暗中同意值或同意为 NULL 的列。

    • 客户不享有对指标表的叁个或多个列的 SELECT/INSERT 权限。

    • 怀有不一样架构的三个或多少个表使用同一个数据文件。

     

  • 数据文件和表的列顺序不一样。

  • 数据文件列的结束字符或前缀长度不一样。

 

此间不利用格式化文件实行导出导入的演示了。详细介绍与行使,请参见联机丛书。

 

2.2 导入数据

动用BULK
INSERT把多少导入到指标表数据。为拉长质量,可不经常删除索引,导完事后再重新建立索引等。请留意要留下丰硕的磁盘空间。这里大致花了15分钟导完。

图片 11

figure-11

 

 

3. 扩展

3.1
数据导出导入自动化与数据接口

出于专门的职业事关,有的时候要开支一些客商的多寡接口,每一日活动导入超级大方的数据。限定于应用程序等要素影响,所以构思直接接收SQL
SE奥迪R8VE奥迪Q5的BULK
INSERT每一天活动去读取相关目录的中游文件。固然目录是动态的,但鉴于中等文件是固定格式的,通过编写制定动态SQL,最终封装成存款和储蓄进程,放到JOB中,配置运营的陈设,就可以到位自动化的做事。上边轻便演示下进程:

 

3.1.1 编写导入脚本

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

code-8

 

3.1.2 配置JOB

首先要布局好的是SQL SEHighlanderVECR-V有权力读取相关目录和文书的权力。在Sql Server
Configuration Manager –> SQL Server Services
选拔相应的实例,右键选取属性,在Log On页签,使用有足够权限运营SQL
SE本田UR-VVE智跑和有权力读取相关目录的客商,举个例子读取互连网盘。

图片 12

figure-12

 

在SQL Server Agent新建叁个功课

图片 13

figure-13

 

在General页,选用Owner,这里接收sa。

图片 14

figure-14

 

在Steps页,在Command里进行写好的积存进程。

图片 15

figure-15

 

在Schedules页,配置实行的时日和成效等。实现。

图片 16

figure-16

 

 

3.2 高版本数据库降级到低版本

貌似的话,从低版本备份的数据库能够一直在高版本的数据库中回复的,例如SQL二零零一的备份能够在SQL二〇〇六或SQL二零零六中回复,除非是跨度太大的之外。例如SQL二〇〇四的备份就不可能一向在SQL二零一一中还原,只可以苏醒到SQL二〇〇八,再从SQL二〇〇八备份出来,最后到SQL二零一三上过来。

而高版本的备份日常不能在低版本中回复,如SQL二零零六的备份无法在SQL二零零七或SQL二零零一中复苏。而实际中,却又会超越这种需求。最佳是透过高版本SSMS直接连接多个例外版本的数据库,通过数据库间的数目导出导入或写剧本,把高版本的数码导到低版本的数据库中。那是相比赶快安全的法子。可是即使四个本子的数据库不可能持续,只可以是把数据导出来,再导入。对于数据量非常小以来,使用SSMS的导出导入作用,或是生成满含数据的台本就可以(下图卡塔 尔(阿拉伯语:قطر‎。对于大额以来,却是一个不幸,如前方有二零零三万数量的大表,生成数据的台本也可以有几个G大,直接接纳SSMS实施是不容许的了。只好是应用SQLCMD实用工具,在后台实践SQL脚本,可能依靠BCP、BULK
INSERT等这种大体量数据导出导入的工具。

图片 17

figure-17

 

4. 总结

动用BCP并结成BULK
INSERT可达成大体积数据的高效导出导入,并能够实现其自动化职业。对于一丢丢数目来讲,操作也不算很复杂。那是除了SSMS上的图形化学工业具之外,又一个不胜实用的工具。

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注