您的位置:1010cc时时彩经典版 > 1010cc安卓版 > SQLServer导数据到Oracle,BCP导出导入大容量数据实

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

发布时间:2019-09-01 04:33编辑:1010cc安卓版浏览(150)

    即使导出时还索要做一些数据的处理,譬喻多表关联,字符管理等,比较复杂的逻辑,最棒是做成存款和储蓄进度,BCP直接调用存款和储蓄进度就能够。

    code-1

    1.OKugaGANIZATION EXTEWranglerNAL重要字,必定要有。以证明定义的表为外界表。

    04.使用SqlBulkCopy

    BCP的有所参数:

    使用Oracle的SQL*LOADE普拉多导入平面文件。固然Oracle中有曾经创建好的表,与导入文本对应。

    从个人来说,作者更爱好使用第三种跟queryout挑选一同使用的写法,因为那样可以进一步灵敏决定要导出的多少。如若实行BCP命令遭逢这么的一无所能提示:

    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;
    

     03.使用BCP进行导出导入

    在尝试了前头五个作用低下的工具之后,大家到底发轫尝试下SQL Server中特意用于导数据的工具:BCP。

    至于BCP的事无巨细用法能够参见MSDN的帮助文书档案。

    咱俩先选择BCP导出多少。

    图片 1

    -U和-P后边分别为数据库的客户名和密码。

    图片 2

    我们得以见到100万的数目导出仅用了1.8秒。

    于今大家再使用BCP举办导入。

    图片 3

    举办后发觉,导入数据选取了20.8秒,还是比较快的。

    图片 4

    • 用时1.872秒 20.810秒=22.682秒
    •  

    在实行上述命令的时候只怕会报错 : 错误提醒:音讯 15281,等第 16,状态 1,进度 xp_cmdshell,第 1 行

    [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
    
    EXEC sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
    
    EXEC sp_configure 'xp_cmdshell', 0
    RECONFIGURE
    GO
    

     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"
    

     01.使用SQL Server Import and Export Tool

    选拔SQL Server Import and Export Tool举行数量的导出,也得以在对象数据库端使用Import进行导入,那有个别套件也是SSIS的一有的。

    在源数据库上右键,选拔Task -> Export Data

    图片 5

    各自填写源数据库和对象数据库的接连消息。

    图片 6

     

    图片 7

     

    选择“copy data from one or more tables or views”

    分选必要导数据的表,况兼能够编写列的Mapping关系。

    图片 8

    能够采用马上实行或然存款和储蓄为SSIS的包,用于执行安排等其它用途。

    此处大家选拔及时试行。

    图片 9

    留神导入的时候假如遇上如下的失实

    Error 0xc02020f4: Data Flow Task: The column "Tel" cannot be processed because more than one code page (936 and 1252) are specified for it.
    (SQL Server Import and Export Wizard)

    是因为两侧的数据库的Collation设置不平等导致的,必要设置同一的Collation。

    • 用时约1分30秒

     

    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;
    

    figure-15

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

    确立外部表时,必需钦命操作系统文件所利用的相间符号。並且该分隔符有且唯有一个。创设外界表时,不可能含有题目列。若是这些标题音信与外表表的字段类型不等同(如字段内容是number数据类型,而题目消息则是字符型数据,则在询问时就能够出错)。要是数据类型恰巧一致的话,这么些标题音讯Oracle数据库也会作为普通记录来相比。

    当Oracle数据库系统访问这几个操作系统文件的时候,会在那么些文件所在的目录自动成立一个日志文件。无论最后是还是不是访谈成功,那几个日志文件都会如期构造建设。查看那些日志文件,能够掌握数据库访谈外界表的效用、是不是成功访谈等等。暗许情况下,该日记在与外表表的一样directory下发生。

    01.使用SQL Server Import and Export Tool

    图片 10

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

    figure-16

    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.
    

    02.使用Generate Scripts生成脚本

    在源数据库上右键,选用Task -> Geneate Scripts...

    图片 11

    布署相关音信,注意选取数据库的版本并将Script Data设置成True。

    图片 12

    这边必要注意,因为有100万的数目,所以导出的SQL文件就有400多M,所以用SQL Server Management Studio是打不开的。

    为此不得不利用sqlcmd试行。

    图片 13sqlcmd语句 

    C:>sqlcmd -i export.sql -d ExportDataDemo_Destination -s 192.168.21.165 -U sa -P 1234567890
    

    用时约28分钟

     

    SQL Server 阻止了对组件 'xp_cmdshell' 的 过程 'sys.xp_cmdshell' 的拜见,因为此组件已当做此服务器安全配置的一局地而被关门。系统一管理理员能够由此采用sp_configure 启用 'xp_cmdshell'。有关启用 'xp_cmdshell' 的详细新闻,请参阅 SQL Server 联机丛书中的 "外围应用配置器"。

    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  
    )
    

    figure-10

    外表表概述

    表面表只可以在Oracle 9i之后来采纳。简单地说,外界表,是指官样文章于数据库中的表。通过向Oracle提供描述外界表的元数据,大家能够把一个操作系统文件正是二个只读的数量库表,就像那些多少存款和储蓄在贰个惯常数据库表中同样来进展拜会。外界表是对数据库表的延长。

    07.结实相比较

    因为这里测量检验的遭逢有网络和表结构的例外情况,无法证实全数意况下效果的差距,可是也可看成参谋之用。

    上面给出比较结实。

     图片 14

    1. BCP 可相信任连接连到远程服务器导出查询
    • 字符编码
    • 字段分隔符
    • 行终止符
    • 日子或时刻格式
    • 特殊字符
    • 导入字段的顺序
    • 导文件文件的表字段类型和尺寸是不是适用

    比较BCP的导入,BULK INSERT提供更加灵敏的选项。

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

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

    00.起家测验情形

    EXEC master..xp_cmdshell 'BCP  test.dbo.name out d:t_002.txt -c -t -T'
    
    EXEC master..xp_cmdshell 'BCP  "select  name from test.dbo.name" queryout d:t_004.txt -c -t -T'
    
    EXEC master..xp_cmdshell 'BCP tran_test.dbo.uptrans out d:t_006.txt -c -t -S HOUYAJUNJHIDCDBS005 -T'
    

    以下是日记文件,显示数据导入的有些音讯。成功导入了18495032行记录,未有导入失利的记录。

    图片 15

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

    05.用到Linked Server实行数据迁移

    1. BCP 可靠任连接到导出查询
    1. 使用SSMS的导出数据向导,使用Microsoft ODBC for Oracle或Oracle Provider for OLE DB连接受Oracle
    2. 导出到平面文件
    3. 导出包蕴数据的SQL脚本。
    4. 使用ETL工具。
    5. 友善开采软件。

    2.2 导入数据

    4.删减外界表可能目录对象

    一般景况下,先删除却界表,然后再删除目录对象,假若目录对象中有多个表,应除去全体表之后再删除目录对象。
    一经在未删减外部表的景况下,强制删除了目录,在查询到被去除的表面表时,将收到"对象不设有"的错误消息。
    查询dba_external_locations来得到当前有所的目录对象以及有关的表面表,相同的时候会付出那几个外部表所对应的操作系统文件的名字。 若是只是在数据库层面上删除此之外界表,并不会自动删除操作系统上的外表表文件。

    06.施用RedGate的SQL Data Compare举行多少迁移

    其三方的工具,有数据库结构相比的工具SQL Compare和数目相比工具SQL Data Compare。

    图片 16

    执行

    图片 17

    因为也是生成INSERT的SQL实行的,所以就不做过多比较了,上边已经测量检验过了。

     

     

    图片 18

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

    2.1 导出多少

    表面表的受制性 

    1.SQLLDTiguan方可钦赐多少提交三遍,即ROWS=?, 外界表却未有,这对于大数据量的导入有个别不方例。
    2.sqlldr errors象征同意错误的行数,外界表用REJECT LIMIT UNLIMITED,这么些功用上基本同样。
    3.外界表的列不能够钦命为not nullable,那样就很难拒绝某列为空值的笔录。
    4.外界表无法应用continueif ,如果记录有换行的就比较难管理。

     

    图片 19 

    常用参数:

    使用SQL*LOADE奥德赛注意多少个难题:

     

    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;
    

    00.手无寸铁测验遭遇

    创立贰个测量试验的条件,贰个数据源数据库,版本为SQL Server 2010,二个对象数据库,版本为SQL Server 贰仟。

    尝试意况如下图所示,源数据库使用语句生成了100万的测验数据。

    图片 20

     

    图片 21创立测量检验表并扭转100万的测验数据图片 22

      IF OBJECT_ID('DEMOTABLE') IS NOT NULL 
          DROP TABLE DEMOTABLE
      GO
      CREATE TABLE DEMOTABLE
          (
            COL1 VARCHAR(50) ,
            COL2 VARCHAR(50) ,
            COL3 VARCHAR(50)
          )
      INSERT  INTO DEMOTABLE
             SELECT TOP 1000000
                     NEWID() ,
                     NEWID() ,
                     NEWID()
             FROM    MASTER..SPT_VALUES T1
                     INNER JOIN MASTER..SPT_VALUES T2 ON 1 = 1
                     INNER JOIN MASTER..SPT_VALUES T3 ON 1 = 1
    

     

    图片 23

    接纳sqlldr命令把数量导入到Oracle中。

    EXEC sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
    
    EXEC sp_configure 'xp_cmdshell', 1
    RECONFIGURE
    GO
    

    1.外界表的创导语法

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

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

    Oracle外部表ORACLE_LOADE中华V类型的创制语法详解:

    能够先看下测验的结果

    正文转自:

    但从导出导入的快慢来讲,是最快的,平面文件能够跨不相同的数据库实行搬迁。纵然数量不容忍错过,只可以通过工具来导了,但速度会相对一点也不快。

     

    创办外部表的潜心事项 

    06.使用RedGate的SQL Data Compare

    常用操作:

    使用平面文件迁移数据,最大麻烦是正是特殊字符,或是有破烂数据。倘使原数据满含与字符分隔符同样的字符,如那中间的“||”,或是有部分不可知的字符,如回车,换行符,等。那些字符会形成导入时,分割字段错位,导致导入错误,数据导不全,乃至导入失利。

    图片 24

    3.用到外界文件数量,使用oracle_loader来填充数据来变化外界表

     

     

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

     

    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.
    

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

    05.行使Linked Server进行数量迁移

    先在源数据库上对目的数据库构建Linked Server,或许反过来也行。 

    图片 25图片 26建立Linked Server

     EXEC sp_addlinkedserver @server = 'LinkedServerToDemo',
         @srvproduct = 'Export Data Testing', @provider = 'MSDASQL',
         @provstr = 'DRIVER={SQL Server};SERVER=192.168.21.165;UID=sa;PWD=password;'
    

    图片 27图片 28是用INSERT INTO...SELECT...举办导入

      DECLARE @begin_date DATETIME
      DECLARE @end_date DATETIME
      SELECT  @begin_date = GETDATE()
    
      INSERT  INTO LinkedServerToDemo.ExportDataDemo_Destination.dbo.DEMOTABLE
              SELECT  *
              FROM    ExportDataDemo_Source.dbo.DEMOTABLE
    
      SELECT  @end_date = GETDATE()
     SELECT  DATEDIFF(ms, @begin_date, @end_date) AS '用时/毫秒' 
    

    实践用时

    图片 29

    • 用时7.97分钟

     

    bcp 实用工具能够在 Microsoft SQL Server 实例和客户内定格式的数据文件间大体积复制数据。  使用 bcp 实用工具能够将大量新行导入 SQL Server 表,或将表数据导出到数据文件。 除非与 queryout 选项联合利用,否则使用该实用工具没有须要驾驭 Transact-SQL 知识。 若要将数据导入表中,必须运用为该表创制的格式文件,大概必得了通大便的结构以及对于该表中的列有效的数据类型。

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

    code-5

    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;
    

    02.使用Generate Scripts

    1. BCP的导入 只要求把地点的 OUT 变为 in 就可以了。

      EXEC master..xp_cmdshell 'BCP test.dbo.name in d:t_002.txt -c -t -U -T'

    暗中同意下,生成的日志文件在当前目录下。无论成功与否,必供给查看日志。看看是或不是导入成功或失败,或是部分成功。导入的难题一般从日记文件就能够找到。

    那边运用-w参数。BCP能够在CMD下导出多少,测验导出三千万条记下,作者的记录本使用了近8分钟左右的小时。BCP同不平时候也能够在SSMS中实践,使用了6分多钟时间,比CMD下速度要快些,生成的文件大小一致,每一个文件近5GB。

    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'));
    

     

    -- 允许配置高级选项  
    EXEC master.sys.sp_configure 'show advanced options', 1  
    -- 重新配置  
    RECONFIGURE  
    -- 启用xp_cmdshell  
    EXEC master.sys.sp_configure 'xp_cmdshell', 1  
    --重新配置  
    RECONFIGURE  
    

    从SQLServer导数据到Oracle差非常的少有以下二种艺术:

    figure-8

    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
    

     

     04.使用SqlBulkCopy

    .NET Framework 2.0中加进的SqlBulkCopy类可以开展飞快的多寡迁移动作,这也为代码完毕数量迁移提供了接口。

    何况SqlBulkCopy类提供了退换字段Mapping关系的形式ColumnMappings。

    图片 30图片 31 使用SqlBulkCopy类实行数据迁移

      using System;
      using System.Data;
      using System.Data.SqlClient;
    
      namespace BulkInsert
      {
          static class Program
          {
              static void Main()
             {
                 DateTime dateTimeStart = DateTime.Now;
                 Console.WriteLine("Start Insert:"   dateTimeStart.ToString("HH:mm:ss fff"));
                 //导入导出的数据库连接
                 SqlConnection connectionDestination = new SqlConnection("Server =.; User ID=datascan; Password=DTSbsd7188228; Initial CataLog=ExportDataDemo_Destination;");
                 SqlConnection connectionSource = new SqlConnection("Server =.; User ID=datascan; Password=DTSbsd7188228; Initial CataLog=ExportDataDemo_Source;");
    
                 //实例化一个SqlBulkCopy
                 var bulker = new SqlBulkCopy(connectionDestination) { DestinationTableName = "DEMOTABLE", BulkCopyTimeout = 600 };
    
                 //获取源数据库的数据
                 SqlCommand sqlcmd = new SqlCommand("SELECT * FROM DEMOTABLE", connectionSource);
                 SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlcmd);
                 DataTable dataTableSource = new DataTable();
                 sqlDataAdapter.Fill(dataTableSource);
    
                 //可以重新定义字段的Mapping关系
                 //SqlBulkCopyColumnMapping sqlBulkCopyColumnMapping = new SqlBulkCopyColumnMapping("COL1", "NEW_COL1");
                 //bulker.ColumnMappings.Add(sqlBulkCopyColumnMapping);
                 connectionDestination.Open();
                 bulker.WriteToServer(dataTableSource);
                 bulker.Close();
                 DateTime dateTimeEnd = DateTime.Now;
                 Console.WriteLine("Insert Ending:"   dateTimeEnd.ToString("HH:mm:ss fff"));
             }
         }
     }
    

    执行后

    图片 32

    • 用时14.8秒

     

    用windows认证登录并把text.dbo.name的表导出到D盘的 t_001.txt

    应用BCP合适导出大容积数据。这里导出千万等级的多少,也是快速就能够不辱义务。

    figure-6

    3.在创建有时表时的相干限制

    对表中字段的称号存在特殊字符的事态下,必需使用阿拉伯语状态的下的双引号将该表列名称连接起来。如采用”SalseID#”。
    对于列名字中特殊符号未使用双引号括起来时,会产生不能平时查询数据。
    建议并非选取异乎通常的列标题字符
    在创建外界表的时候,并不以前在数据库中创制表,也不会为外部表分配任何的囤积空间。
    创设外界表只是在数额字典中开创了表面表的元数据,以便对应访谈外界表中的数码,而不在数据库中存款和储蓄外界表的数额。
    粗略地说,数据仓库储存款和储蓄的只是与外表文件的一种对应提到,如字段与字段的附和关系。而从不存款和储蓄实际的数码。
    由于存款和储蓄实际多少,故无法为外部表创设索引,同一时候在数码利用DML时也不援助对外界表的插入、更新、删除等操作。

    做事中有段时光时有时提到到不一致版本的数据库间导出导入数据的主题素材,索性整理一下,并简要比较下质量,有所遗漏的方法也款待商量、补充。

    用完以后再把其关闭,关闭只必要把 1 变为 0 就能够。 上面包车型大巴言辞也足以经过输入顾客名和密码的花样出来,如下例句:

    借使有错误,还有大概会变动与导入文本同名的t1.bad文件。

     

    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.
    

    07.结出相比较

    -f format_file format_file表示格式文件名。这一个选项注重于上述的动作,假如选拔的是in或out,format_file表示已经存在的格式文件,假诺应用的是format则代表是要转移的格式文件。

    -x 那么些选项要和-f format_file合作使用,以便生成xml格式的格式文件。

    -F first_row 钦定从被导出表的哪一行导出,或从被导入文本的哪一行导入。

    -L last_row 钦点被导出表要导到哪一行截止,或从被导入文本导数据时,导到哪一行截止。

    -c 使用char类型做为存储类型,未有前缀且以"t"做为字段分割符,以"n"做为行分割符。

    -w 和-c类似,只是当使用Unicode字符集拷贝数据时行使,且以nchar做为存款和储蓄类型。

    -t field_term 钦命字符分割符,暗中同意是"t"。

    -r row_term 钦命行分割符,暗许是"n"。

    -S server_name[ instance_name] 钦赐要连接的SQL Server服务器的实例,假使未钦赐此选项,BCP连接本机的SQL Server私下认可实例。要是要连接某台机器上的默许实例,只需求内定机器名即可。

    -U login_id 指定连接SQL Sever的顾客名。

    -P password 钦命连接SQL Server的顾客名密码。

    -T 钦点BCP使用信任连接登陆SQL Server。要是未内定-T,必须内定-U和-P。

    -k 钦命空驶列车使用null值插入,而不是那列的暗中认可值。

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

    5.ACCESS PARAMETEENVISIONS:描述如何对表面表进行访谈

    RECO帕杰罗DS关键字后定义怎样分辨数据行  
    DELIMITED BY 'XXX'——换行符,常用newline定义换行,并指明字符集。对于特种的字符则需求独自定义,如特殊符号,能够使用OX'十六个人值',举例tab(/t)的十三位是9,则DELIMITEDBY0X'09';
    cr(/r)的十四个人是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,。
    LRTLacrosseIM ——删除首尾空白字符。
    MISSING FIELD VALUES ARE NULL——有些字段空缺值都设为NULL。
    对于字段长度和分割符不鲜明且计划作为外界表文件,可以选拔UltraEdit、Editplus等来开展深入分析测量检验,假若文件异常的大,则必要思量将文件分割成小文件并从中提取数额举行测量检验。

    03.使用BCP

     

     

    创办外界表 

    运用CREATE TABLE语句的O奥迪Q3GANIZATION EXTENERAL子句来创设外界表。外界表不分配任何盘区,因为只有是在数额字典中开创元数据。

    1. BCP 可信赖任连接到本地球表面导出:
    • 全部分裂框架结构的五个表使用同样数据文件作为数据源。

    • 数据文件中的字段数区别于指标表中的列数;比如:

      • 对象表中至少含有贰个概念了暗中认可值或同意为 NULL 的列。

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

      • 全体不一样架构的三个或四个表使用同三个数据文件。

       

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

    • 数据文件列的苏息字符或前缀长度分裂。

    a.查看外表表消息

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

     

    也得以选拔上边包车型地铁T-SQL命令:

    figure-7

    g.创造符合规律的表,将表面表数据导入,那正是选择ORACLE_DATAPUMP类型的额外界表完成数据迁移

    create table tb1 as select * from in_tb1;
    

    (1) 导入 这几个动作使用in命令达成,前边跟需求导入的文本名。 (2) 导出 这些动作使用out命令落成,前边跟必要导出的文书名。 (3) 使用SQL语句导出 那么些动作使用queryout命令完结,它跟out类似,只是数据源不是表或视图名,而是SQL语句。 (4) 导出格式文件 那一个动作使用format命令完结,后而跟格式文件名。

     

     4.外界表相关视图

    图片 33;)

    code-2

    外表表对不当的拍卖 

    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中华V不足的情况,由此尽也许的增大READSIZE
    2.换行符不对发生的标题。在分裂的操作系统中换行符的象征方法不平等,遇到错误日志提示如是换行符难题,能够接纳
    UltraEdit张开,直接看十六进制
    3.特定行报错开上下班时间,查看带有"BAD"的日记文件,当中保存了失误的数量,用记事本展开看看这里出错,是还是不是留存于表面表定义相争执

    本条是对刚刚导出的数据进行再次的导入,能够很显著的看看表中的数量现已扩充了。

     

    1.亟需先创建目录对象

    在建设构造指标的时候,须要小心,Oracle数据库系统不会去断定那个目录是不是真的存在。如若在输入这些目录对象的时候,相当的大心把门路写错了,那或然那个外部表照旧能够正常创建,但是却一点办法也没有查询到数量。由于建构目录对象时,缺少这种本身反省的机制,为此在将路线赋予给那几个目录对象时,需求专门的小心。别的部需要要留意的是路线的深浅写。在Windows操作系统中,其路径是不区分轻重缓急写的。而在Linux操作系统,那个渠道需求区分轻重缓急写。故在不相同的操作系统 中,创建目录对象时索要小心那么些分寸写的差异

    BCP能够实行的4种操作

    本文由1010cc时时彩经典版发布于1010cc安卓版,转载请注明出处:SQLServer导数据到Oracle,BCP导出导入大容量数据实

    关键词: