SQLServer导数据到Oracle,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 SEWranglerVE途达提供多样差别的数据导出导入的工具,也得以编写制定SQL脚本,使用存款和储蓄进度,生成所需的数据文件,以致能够转换包涵SQL语句和数据的台本文件。各有利害,以适用不一样的须求。上面介绍大体积数据导出导入的利器——BCP实用工具。同期在后头也介绍BULK INSERT导入大体量数据,以及BCP结合BULK INSERT做多少接口的实施(在SQL二〇〇九Rubicon2上实践)。

表面表的特色 

身处文件系统之中,按一定格式分割,如文本文件可能其余类其余表能够作为外界表。
对外界表的探望能够透过SQL语句来完结,而无需先将表面表中的数据装载进数据库中。
外界数据表都以只读的,由此在外界表不能实行DML操作,也无法成立索引。
ANALYZE语句不援救收罗外界表的总计数据,应该使用DMBS_STATS包来收罗外界表的总计数据。

00.创立测验情状

以下使用第2种情势来扩充数据迁移的。

 

创办外界表的小心事项 

01.使用SQL Server Import and Export Tool

运用BCP合适导出大体积数据。这里导出千万级其余数量,也是全速就会打响。

1. BCP的用法

1.亟需先创立目录对象

在创设目标的时候,需求小心,Oracle数据库系统不会去分明这些目录是不是真的存在。假若在输入这几个目录对象的时候,一点都不小心把路子写错了,那只怕那几个外部表依然能够符合规律建构,然而却一点办法也未有查询到数量。由于建构目录对象时,缺乏这种自己检查的机制,为此在将路线赋予给这几个目录对象时,需求特意的注意。别的必要注意的是路线的尺寸写。在Windows操作系统中,其路线是不区分轻重缓急写的。而在Linux操作系统,这一个路子要求区分轻重缓急写。故在不相同的操作系统 中,创立目录对象时索要留心那个尺寸写的差异

02.使用Generate Scripts

倘使导出时还须求做一些数目标管理,比方多表关联,字符管理等,比较复杂的逻辑,最佳是做成存款和储蓄进程,BCP直接调用存款和储蓄进度就可以。

BCP 实用工具能够在 Microsoft SQL Server 实例和客商钦点格式的数据文件间大体量复制数据。使用 BCP实用工具能够将大气新行导入 SQL Server 表,或将表数据导入数据文件。除非与 queryout 选项一同使用,不然使用该实用工具无需领会 Transact-SQL 知识。BCP不仅可以够在CMD提醒符下运营,也足以在SSMS下进行。

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

创立外界表时,必需钦定操作系统文件所利用的相间符号。何况该分隔符有且只有三个。制造外界表时,不能够含有标题列。假设那个标题消息与外表表的字段类型不一致样(如字段内容是number数据类型,而标题音讯则是字符型数据,则在查询时就能出错)。要是数据类型恰巧一致的话,这一个标题消息Oracle数据库也会作为普通记录来相比较。

当Oracle数据库系统访谈这些操作系统文件的时候,会在这几个文件所在的目录自动成立八个日记文件。无论最后是或不是访问成功,这一个日志文件都会准时创立。查看这么些日志文件,能够领悟数据库访问外界表的频率、是不是成功访谈等等。默许情状下,该日记在与表面表的完全一样directory下发生。

03.使用BCP

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时也不支持对外界表的插入、更新、删除等操作。

04.使用SqlBulkCopy

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

figure-1

4.刨除此而外部表或许目录对象

貌似情状下,先删除却部表,然后再删除目录对象,倘诺目录对象中有七个表,应除去全体表之后再删除目录对象。
倘若在未删减外界表的境况下,强制删除了目录,在询问到被剔除的表面表时,将吸收接纳"对象不真实"的错误音讯。
查询dba_external_locations来获得当前有着的目录对象以及有关的表面表,同不经常候会付给这一个外界表所对应的操作系统文件的名字。 若是只是在数据库层面上剔除此之外界表,并不会自动删除操作系统上的表面表文件。

05.行使Linked Server实行多少迁移

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

 

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

区别的操作系统对于外界表有分歧的表达和显示格局
如在Linux操作系统中开创的文本是分号分隔且每行一条记下,但该公文在Windows操作系统上打开则并不是那样。
建议制止差别操作系统以及分歧字符集所带来的熏陶

06.使用RedGate的SQL Data Compare

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

语法:

创办外界表 

利用CREATE TABLE语句的OXC60GANIZATION EXTENERAL子句来创制外部表。外界表不分红任何盘区,因为独有是在数量字典中创立元数据。

07.结果相比较

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福特Explorer类型的成立语法详解:

能够先看下测量检验的结果

使用SQL*LOADEPRADO注意几个难题:

 

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

图片 2 

  • 字符编码
  • 字段分隔符
  • 行终止符
  • 日子或时刻格式
  • 特殊字符
  • 导入字段的顺序
  • 导文件文件的表字段类型和长短是或不是适宜

归纳的导出例子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中。

图片 3

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.

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

00.起家测量试验情状

确立三个测验的情状,一个数据源数据库,版本为SQL Server 二〇一〇,一个指标数据库,版本为SQL Server 三千。

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

图片 4

 

图片 5确立测验表并扭转100万的测量检验数据图片 6

  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

 

暗中认可下,生成的日记文件在当前目录下。无论成功与否,必定要查阅日志。看看是还是不是导入成功或破产,或是部分成功。导入的标题一般从日记文件就可以找到。

 

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

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

 01.使用SQL Server Import and Export Tool

利用SQL Server Import and Export Tool实行多少的导出,也得以在目的数据库端使用Import实行导入,那部分套件也是SSIS的一有些。

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

图片 7

个别填写源数据库和指标数据库的连天音信。

图片 8

 

图片 9

 

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

选料须要导数据的表,而且能够编写列的Mapping关系。

图片 10

能够挑选立刻推行只怕存款和储蓄为SSIS的包,用于推行安排等任何用途。

此地大家采纳即刻推行。

图片 11

只顾导入的时候纵然蒙受如下的不当

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秒

假如有荒唐,还有大概会生成与导入文本同名的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'));

02.施用Generate Scripts生成脚本

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

图片 12

安排相关音讯,注意选择数据库的版本并将Script Data设置成True。

图片 13

此处须要注意,因为有100万的数据,所以导出的SQL文件就有400多M,所以用SQL Server Management Studio是打不开的。

进而不得不采用sqlcmd试行。

图片 14sqlcmd语句 

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

用时约28分钟

 

以下是日记文件,显示数据导入的部分新闻。成功导入了18495032行记录,未有导入战败的记录。

图片 15

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.

 03.使用BCP举行导出导入

在品尝了前头五个效能低下的工具之后,大家算是开首尝试下SQL Server中等专门的学问高校门用来导数据的工具:BCP。

至于BCP的详实用法能够参见MSDN的帮忙文书档案。

我们先选用BCP导出多少。

图片 16

-U和-P前面分别为数据库的客商名和密码。

图片 17

大家能够看出100万的数码导出仅用了1.8秒。

至今我们再使用BCP进行导入。

图片 18

实行后发觉,导入数据运用了20.8秒,依然相当慢的。

图片 19

  • 用时1.872秒 20.810秒=22.682秒
  •  
[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;

 04.使用SqlBulkCopy

.NET Framework 2.0中增添的SqlBulkCopy类能够打开急速的数据迁移动作,这也为代码达成多少迁移提供了接口。

再正是SqlBulkCopy类提供了修改字段Mapping关系的方法ColumnMappings。

图片 20图片 21 使用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"));
         }
     }
 }

执行后

图片 22

  • 用时14.8秒

 

行使平面文件迁移数据,最大麻烦是就是特殊字符,或是有破烂数据。假使原数据包蕴与字符分隔符同样的字符,如那其间的“||”,或是有一对不可知的字符,如回车,换行符,等。那个字符会造成导入时,分割字段错位,导致导入错误,数据导不全,乃至导入退步。

 

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

05.采纳Linked Server进行数据迁移

先在源数据库上对指标数据库营造Linked Server,或许反过来也行。 

图片 23图片 24建立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;'

图片 25图片 26是用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 '用时/毫秒' 

奉行用时

图片 27

  • 用时7.97分钟

 

但从导出导入的快慢来讲,是最快的,平面文件能够跨不一样的数据库举行搬迁。假设数据不容忍错失,只能通过工具来导了,但速度会相对异常慢。

在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"

06.利用RedGate的SQL Data Compare进行数据迁移

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

图片 28

执行

图片 29

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

 

 

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;

07.结果比较

因为这边测试的意况有互连网和表结构的特有情况,不能够表达全部情状下效果的差别,可是也可作为参照之用。

上边给出比较结实。

 图片 30

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.表面表相关视图

图片 31

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哈弗GANIZATION EXTEPRADONAL第一字,必供给有。以注脚定义的表为外界表。

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

2..器重参数外界表的门类

ORACLE_LOADEWrangler:定义外界表的缺省情势,只好只读情势完结文件数据的装载。
ORACLE_DATAPUMP:协助对数码的装载与卸载,数据文件必需为二进制dump文件。能够从外表表提取数额装载到内部表,也得以从里面表卸载数据作为二进制文件填充到外界表。

code-2

3.DEFAULT DIRECTOEvoqueY:缺省的目录指明了外界文件所在的路线

 

 

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

图片 32

5.ACCESS PARAMETE凯雷德S:描述怎么样对表面表实行访问

RECOCR-VDS关键字后定义怎样分辨数据行  
DELIMITED BY 'XXX'——换行符,常用newline定义换行,并指明字符集。对于极度的字符则供给独自定义,如特殊符号,能够使用OX'十几人值',比如tab(/t)的十五人是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,第1个字段值是a"b,"c,。
LRTSportageIM ——删除首尾空白字符。
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.外界表平日遭遇BUFFELAND不足的动静,由此尽也许的增大READSIZE
2.换行符不对产生的标题。在分歧的操作系统中换行符的表示方法不雷同,碰着错误日志提醒如是换行符难题,能够利用
UltraEdit打开,直接看十六进制
3.特定行报错开上下班时间,查看带有"BAD"的日记文件,当中保存了失误的数额,用记事本展开看看这里出错,是不是留存于外界表定义相顶牛

 

表面表的局限性 

1.SQLLD兰德Enclave得以钦命多少提交三次,即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就能够,把数量导入。

图片 33

figure-6

 

图片 34

figure-7

 

选用BULK INSERT导入数据

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

code-5

 

图片 35

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的导入,下边进行部分实际上的操作。为了好像实际条件,创设一张十一个字段的表,包括有两种常用的数据类型,构造三千万的数量,包括汉语和法语。为了更加快插入测量试验数据,先不创立索引。在执行下边代码在此之前,请留神下数据库的日记苏醒方式是或不是设置为大体积方式或简捷格局,以及磁盘空间是不是丰裕(笔者的实施中,数据变动后数据文件和日志文件大致必要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。

图片 36

figure-9

 

图片 37

figure-10

 

而对于复杂的大体量导入情形,平常都会须要格式化文件。在偏下意况下,必需利用格式化文件:

  • 享有分化架构的七个表使用同一数据文件作为数据源。

  • 数据文件中的字段数分歧于指标表中的列数;举个例子:

    • 目的表中至少含有二个概念了暗中同意值或允许为 NULL 的列。

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

    • 享有不相同框架结构的八个或七个表使用同贰个数据文件。

     

  • 数据文件和表的列顺序区别。

  • 数据文件列的休憩字符或前缀长度区别。

 

这里不利用格式化文件实行导出导入的亲自过问了。详细介绍与利用,请参见联机丛书。

 

2.2 导入数据

使用BULK INSERT把多少导入到目的表数据。为增进品质,可一时删除索引,导完现在再重新建立索引等。请留神要留下丰裕的磁盘空间。这里大致花了15秒钟导完。

图片 38

figure-11

 

 

3. 扩展

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

由于专门的工作关系,有时要付出一些顾客的多少接口,每一天活动导入异常的大方的多寡。限制于应用程序等要素影响,所以思索直接选择SQL SEQashqaiVE陆风X8的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 SE翼虎VE奥迪Q5有权力读取相关目录和文书的权杖。在Sql Server Configuration Manager --> SQL Server Services 选取相应的实例,右键选拔属性,在Log On页签,使用有丰富权限运行SQL SE奥迪Q5VEMercedes-迈巴赫和有权力读取相关目录的客户,比如读取互连网盘。

图片 39

figure-12

 

在SQL Server Agent新建一个学业

图片 40

figure-13

 

在General页,选择Owner,这里选用sa。

图片 41

figure-14

 

在Steps页,在Command里试行写好的存储进度。

图片 42

figure-15

 

在Schedules页,配置施行的时日和频率等。达成。

图片 43

figure-16

 

 

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

貌似的话,从低版本备份的数据库能够直接在高版本的数据库中平复的,举个例子SQL两千的备份能够在SQL二〇〇七或SQL二〇一〇中恢复生机,除非是跨度太大的之外。比方SQL三千的备份就无法一直在SQL2011中回复,只好恢复生机到SQL贰零零玖,再从SQL2009备份出来,最终到SQL二〇一三上复苏。

而高版本的备份一般不能够在低版本中平复,如SQL二零零六的备份不可能在SQL二〇〇六或SQL三千中复苏。而事实上中,却又会遭遇这种供给。最棒是透过高版本SSMS直接连接八个不等版本的数据库,通过数据库间的数码导出导入或写剧本,把高版本的数额导到低版本的数据库中。那是比极红速安全的法子。不过倘使多少个版本的数据库不可能循环不断,只可以是把数据导出来,再导入。对于数据量非常小的话,使用SSMS的导出导入效率,或是生成包蕴数据的台本就可以(下图)。对于大额以来,却是贰个苦难,如前方有3000万数额的大表,生成数据的本子也可以有多少个G大,直接行使SSMS实施是不可能的了。只好是行使SQLCMD实用工具,在后台推行SQL脚本,也许借助BCP、BULK INSERT等这种大体量数据导出导入的工具。

图片 44

figure-17

 

4. 总结

选择BCP并组成BULK INSERT可实现大体量数据的迅猛导出导入,并得以兑现其自动化职业。对于一丢丢数据的话,操作也不算很复杂。那是除了SSMS上的图形化学工业具之外,又叁个不胜实用的工具。

 

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:SQLServer导数据到Oracle,Oracle外界表详解

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。