图片 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
SEPAJEROVE奥迪Q5提供多样分化的多寡导出导入的工具,也足以编写制定SQL脚本,使用存款和储蓄进程,生成所需的数据文件,以至足以生成包蕴SQL语句和多少的本子文件。各有利害,以适用分裂的需要。上面介绍大体积数据导出导入的利器——BCP实用工具。同一时间在背后也介绍BULK
INSERT导入大容积数据,以致BCP结合BULK
INSERT做多少接口的实行(在SQL二〇〇八Enclave2上施行卡塔 尔(阿拉伯语:قطر‎。

表面表的特征 

座落文件系统之中,按自然格式分割,如文本文件大概别的品类的表能够用作外界表。
对外界表的拜候能够经过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.在确立不时表时的有关约束

对表中字段的名称存在特殊字符的图景下,必得使用乌Crane语状态的下的双引号将该表列名称连接起来。如运用”SalseID#”。
对于列名字中特殊符号未选择双引号括起来时,会促成不大概正常查询数据。
提议不用选择极度的列标题字符
在创制外界表的时候,并不曾经在数据库中成立表,也不会为外部表分配任何的仓库储存空间。
制造外界表只是在数据字典中创建了外界表的元数据,以便对应访谈外界表中的数码,而不在数据库中存款和储蓄外界表的多少。
简简单单地说,数据仓库储存款和储蓄的只是与外表文件的大器晚成种对应涉及,如字段与字段的应和关系。而未有存款和储蓄实际的数码。
出于存款和储蓄实际多少,故不能为外界表创立索引,同期在数码利用DML时也不帮衬对外界表的插入、更新、删除等操作。

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

figure-1

4.刨除此之外界表可能目录对象

貌似景观下,先删除此而外界表,然后再删除目录对象,如果目录对象中有四个表,应除去全部表之后再删除目录对象。
尽管在未删减外界表的动静下,强制删除了目录,在询问到被删除的外界表时,将吸收接纳”对象官样文章”的错误新闻。
查询dba_external_locations来取妥善前具备的目录对象以致有关的外界表,同一时候会付出那么些外界表所对应的操作系统文件的名字。 假若只是在数据库层面上删除了那些之外界表,并不会活动删除操作系统上的外界表文件。

使用Oracle的SQL*LOADE陆风X8导入平面文件。假若Oracle中有已经创办好的表,与导入文本对应。

 

 5.对于操作系统平台的范围

昔不目前的操作系统对于外界表有区别的表明和彰显格局
如在Linux操作系统中开创的文件是分号分隔且每行一条记下,但该文件在Windows操作系统上开垦则并不是那样。
建议幸免分裂操作系统甚至不一致字符集所推动的震慑

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

语法:

创建国门外界表 

使用CREATE TABLE语句的O奥迪Q7GANIZATION
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_LOADEHuracán类型的创建语法详整:

使用SQL*LOADECR-V注意多少个难题:

 

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.O本田CR-VGANIZATION EXTE奔驰M级NAL尤为重要字,必供给有。以标注定义的表为外界表。

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

2..着重参数外界表的品种

ORACLE_LOADEKuga:定义外界表的缺省方式,只好只读方式落到实处公文数据的装载。
ORACLE_DATAPUMP:帮忙对数据的装载与卸载,数据文件必需为二进制dump文件。能够从外表表提取数额装载到里面表,也能够从里面表卸载数据作为二进制文件填充到外界表。

code-2

3.DEFAULT DIRECTO奥迪Q3Y:缺省的目录指明了表面文件所在的路子

 

 

4.LOCATION:定义了表面表的职位

图片 5

5.ACCESS PARAMETE兰德KoleosS:描述怎么着对表面表展开拜访

RECO奥迪Q5DS关键字后定义怎么着识别数据行  
DELIMITED BY
‘XXX’——换行符,常用newline定义换行,并指明字符集。对于特种的字符则必要独自定义,如特殊符号,能够使用OX’十四位值’,比方tab(/t)的十二人是9,则DELIMITEDBY0X’09’;
cr(/r)的15人是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,第叁个字段值是a”b,”c,。
LRTXC90IM ——删除首尾空白字符。
MISSING FIELD VALUES ARE NULL——某些字段空缺值都设为NULL。
对此字段长度和分割符不鲜明且酌量作为外界表文件,能够使用UltraEdit、Editplus等来开展深入分析测量检验,假如文件不小,则须求思量将文件分割成小文件并从当中提取数据开展测验。

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宝马7系不足的动静,由此尽大概的增大READSIZE
2.换行符不对爆发的难题。在分化的操作系统中换行符的代表方法不相仿,境遇错误日志提醒如是换行符难题,能够利用
UltraEdit张开,直接看十四进制
3.特定行报错开上下班时间,查看带有”BAD”的日志文件,当中保存了失误的数量,用记事本展开看看这里出错,是或不是留存于表面表定义相冲突

 

外表表的受制性 

1.SQLLDSportage能够钦命多少提交一回,即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的导入,下边实香港行政局地实际上的操作。为了好像实际条件,创设一张12个字段的表,满含有三种常用的数据类型,构造二〇〇三万的数码,蕴含普通话和德文。为了更快插入测量试验数据,先不创制索引。在实行上面代码早前,请小心下数据库的日志复苏方式是还是不是设置为大体量形式或简捷情势,以至磁盘空间是还是不是充足(小编的推行中,数据变化后数据文件和日志文件大约需求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下导出多少,测验导出二〇〇二万条记下,作者的台式机使用了近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
SERubiconVERubicon的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 SEPAJEROVE悍马H2有权力读取相关目录和文件的权柄。在Sql Server
Configuration Manager –> SQL Server Services
选取相应的实例,右键选取属性,在Log On页签,使用有丰裕权限运维SQL
SE福特ExplorerVEGL450和有权力读取相关目录的客商,比方读取网络盘。

图片 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二零零五或SQL2009中回复,除非是跨度太大的之外。举例SQL2004的备份就无法平素在SQL二〇一二中复苏,只可以苏醒到SQL2009,再从SQL二〇〇八备份出来,最终到SQL二零一一上回复。

而高版本的备份平日无法在低版本中苏醒,如SQL2009的备份无法在SQL二〇〇五或SQL二零零二中回复。而实在中,却又会遇上这种供给。最棒是经过高版本SSMS直接连接三个例外版本的数据库,通过数据库间的数目导出导入或写剧本,把高版本的数额导到低版本的数据库中。那是相比较连忙安全的办法。但是借使八个本子的数据库无法持续,只好是把数据导出来,再导入。对于数据量十分的小的话,使用SSMS的导出导入作用,或是生成富含数据的本子就可以(下图卡塔 尔(阿拉伯语:قطر‎。对于大数量来讲,却是一个不幸,如前方有二〇〇〇万数额的大表,生成数据的剧本也是有多少个G大,直接动用SSMS实践是不容许的了。只可以是选用SQLCMD实用工具,在后台实践SQL脚本,只怕依赖BCP、BULK
INSERT等这种大容积数据导出导入的工具。

图片 17

figure-17

 

4. 总结

应用BCP并结合BULK
INSERT可达成大体量数据的飞快导出导入,并能够完结其自动化职业。对于一些些数量的话,操作也不算很复杂。这是除了SSMS上的图形化学工业具之外,又三个要命实用的工具。

 

发表评论