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
SE翼虎VE大切诺基提供三种分歧的数量导出导入的工具,也能够编写制定SQL脚本,使用存款和储蓄进程,生成所需的数据文件,乃至足以转换满含SQL语句和数码的本子文件。各有利害,以适用不相同的须要。下边介绍大容积数据导出导入的利器——BCP实用工具。同一时间在后头也介绍BULK
INSERT导入大体量数据,以及BCP结合BULK
INSERT做多少接口的奉行(在SQL2009Enclave2上实施)。

表面表的表征 

放在文件系统之中,按自然格式分割,如文本文件或许其余项指标表能够当作外界表。
对外界表的拜望能够通过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:\export\t1.txt -c -t"||" -S"192.168.1.100" -Urpt -Prpt123
pause

USE TestDB
GO

CREATE PROC [dbo].[export_usercar]
AS
    SELECT  [carId]
           ,CONVERT(NVARCHAR(30), [addTime], 120)
           ,CONVERT(NVARCHAR(30), [lastSearchTime], 120)
           ,CONVERT(NVARCHAR(30), [updateTime], 120)
           ,[carType]
           ,[userTelephone]
           ,[isCorrect]
           ,[userId]
           ,[validFlag]
           ,[Channel]
           ,[carCode]
           ,[engineNumber]
           ,[carNumber]
    FROM    [TestDB].[dbo].[t1] WITH ( NOLOCK )
    WHERE   validFlag = 1
            AND isCorrect = 1;

997755.com澳门葡京 1

3.在创造不经常表时的连带限量

对表中字段的称呼存在特殊字符的动静下,必得使用立陶宛语状态的下的双引号将该表列名称连接起来。如应用”SalseID#”。
对于列名字中特殊符号未利用双引号括起来时,会促成不只怕符合规律查询数据。
建议并非选取异乎平常的列标题字符
在开立外界表的时候,并不曾在数据库中创造表,也不会为外界表分配任何的仓库储存空间。
创办外界表只是在多少字典中创设了表面表的元数据,以便对应访谈外界表中的数据,而不在数据库中积存外部表的数量。
简易地说,数据仓库储存款和储蓄的只是与外表文件的一种对应涉及,如字段与字段的应和关系。而从不存款和储蓄实际的数额。
由于存储实际多少,故异常的小概为外界表创立索引,相同的时候在数据运用DML时也不援助对外界表的插入、更新、删除等操作。

04.使用SqlBulkCopy

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

figure-1

4.刨除却界表大概目录对象

诚如景色下,先删除此而外界表,然后再删除目录对象,假诺目录对象中有多个表,应除去全数表之后再删除目录对象。
倘使在未删减外界表的事态下,强制删除了目录,在询问到被删去的表面表时,将接纳”对象不设有”的错误音信。
查询dba_external_locations来取妥当前享有的目录对象以及有关的表面表,同一时间会交到那么些外界表所对应的操作系统文件的名字。 如若只是在数据库层面上删除却界表,并不会活动删除操作系统上的外界表文件。

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

使用Oracle的SQL*LOADE揽胜导入平面文件。纵然Oracle中有已经创设好的表,与导入文本对应。

 

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

不相同的操作系统对于外界表有两样的讲解和呈现格局
如在Linux操作系统中开创的文书是分号分隔且每行一条记下,但该文件在Windows操作系统上开垦则其实不然。
提议避免不一样操作系统以及差别字符集所推动的震慑

06.使用RedGate的SQL Data Compare

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

语法:

成立外界表 

利用CREATE TABLE语句的O摩根RoadsterGANIZATION
EXTENERAL子句来成立外部表。外部表不分红任何盘区,因为唯有是在数据字典中创建元数据。

07.结实相比较

load data
CHARACTERSET 'ZHS16GBK'
infile '/data/import/t1.txt' "str '\r\n'"
into table SCOTT.T1
fields terminated by '||' TRAILING NULLCOLS
(
carId, 
addTime DATE "YYYY-MM-DD HH24:MI:SS",
lastSearchTime DATE "YYYY-MM-DD HH24:MI:SS",
updateTime DATE "YYYY-MM-DD HH24:MI:SS",
carType ,
userTelephone  ,
isCorrect  ,
userId  ,
validFlag ,
Channel ,
carCode  ,
engineNumber ,
carNumber  
)
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*LOADE汉兰达注意几个难题:

 

2.由询问结果集,使用Oracle_SQLServer导数据到Oracle,Oracle外界表详解。datapump来填充数据来扭转外界表

997755.com澳门葡京 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中。

997755.com澳门葡京 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万的测试数据。

997755.com澳门葡京 4

 

997755.com澳门葡京 5确立测验表并扭转100万的测量检验数据997755.com澳门葡京 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

997755.com澳门葡京 7

独家填写源数据库和目的数据库的总是消息。

997755.com澳门葡京 8

 

997755.com澳门葡京 9

 

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

慎选需求导数据的表,並且能够编写制定列的Mapping关系。

997755.com澳门葡京 10

能够选取立时执行大概存款和储蓄为SSIS的包,用于试行安排等其余用途。

这里我们选拔及时实施。

997755.com澳门葡京 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…

997755.com澳门葡京 12

布局相关新闻,注意选用数据库的版本并将Script
Data设置成True。

997755.com澳门葡京 13

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

就此只可以选用sqlcmd实行。

997755.com澳门葡京 14sqlcmd语句 

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

用时约28分钟

 

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

997755.com澳门葡京 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导出多少。

997755.com澳门葡京 16

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

997755.com澳门葡京 17

我们得以看出100万的数额导出仅用了1.8秒。

以后大家再使用BCP举行导入。

997755.com澳门葡京 18

实践后意识,导入数据应用了20.8秒,依旧不慢的。

997755.com澳门葡京 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。

997755.com澳门葡京 20997755.com澳门葡京 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"));
         }
     }
 }

执行后

997755.com澳门葡京 22

  • 用时14.8秒

 

利用平面文件迁移数据,最大麻烦是正是特殊字符,或是有垃圾数据。假设原数据包罗与字符分隔符一样的字符,如那当中的“||”,或是有一对不可知的字符,如回车,换行符,等。这几个字符会形成导入时,分割字段错位,导致导入错误,数据导不全,以致导入退步。

 

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

05.施用Linked Server举办多少迁移

先在源数据库上对指标数据库建设构造Linked
Server,只怕反过来也行。 

997755.com澳门葡京 23997755.com澳门葡京 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;'

997755.com澳门葡京 25997755.com澳门葡京 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 '用时/毫秒' 

施行用时

997755.com澳门葡京 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。

997755.com澳门葡京 28

执行

997755.com澳门葡京 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.结出比较

因为这里测量试验的条件有互连网和表结构的超过常规规情形,不可能印证全数情况下效果的反差,可是也可看作参照他事他说加以考察之用。

上面给出比较结实。

 997755.com澳门葡京 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.外表表相关视图

997755.com澳门葡京 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奥迪Q5GANIZATION EXTESportageNAL注重字,必得求有。以注解定义的表为外界表。

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

2..至关心重视要参数外部表的门类

ORACLE_LOADETiguan:定义外界表的缺省格局,只可以只读格局完结文件数据的装载。
ORACLE_DATAPUMP:援助对数据的装载与卸载,数据文件必须为二进制dump文件。能够从外表表提取数额装载到里头表,也足以从里边表卸载数据作为二进制文件填充到外界表。

code-2

3.DEFAULT DIRECTO凯雷德Y:缺省的目录指明了表面文件所在的路径

 

 

4.LOCATION:定义了外界表的地点

997755.com澳门葡京 32

5.ACCESS PARAMETE宝马7系S:描述如何对外表表张开访谈

RECO猎豹CS6DS关键字后定义怎么样识别数据行  
997755.com澳门葡京,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,。
LRTQashqaiIM ——删除首尾空白字符。
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福睿斯不足的情形,因而尽也许的增大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就能够,把数据导入。

997755.com澳门葡京 33

figure-6

 

997755.com澳门葡京 34

figure-7

 

使用BULK INSERT导入数据

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

code-5

 

997755.com澳门葡京 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(制表符)作为字段分隔符,使用 \r\n(换行符)作为行终止符。
-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_name\instance_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的导入,上面进行一些实在的操作。为了临近实际条件,成立一张13个字段的表,满含有三种常用的数据类型,构造两千万的数码,包罗中文和意国语。为了越来越快插入测验数据,先不成立索引。在进行下边代码以前,请小心下数据库的日记恢复生机形式是或不是设置为大体积情势或简捷方式,以及磁盘空间是或不是丰盛(笔者的试行中,数据变化后数据文件和日志文件差不离须要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 KEN\SQLSERVER08R2'
GO

code-7

 

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

997755.com澳门葡京 36

figure-9

 

997755.com澳门葡京 37

figure-10

 

而对此复杂的大容积导入处境,平日都会必要格式化文件。在偏下情形下,必得选拔格式化文件:

  • 抱有差异架构的多少个表使用相同数据文件作为数据源。

  • 数据文件中的字段数不一样于目的表中的列数;举例:

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

    • 客商不辜负有对指标表的一个或多个列的 SELECT/INSERT 权限。

    • 持有分歧框架结构的七个或四个表使用同二个数据文件。

     

  • 数据文件和表的列顺序差异。

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

 

此间不使用格式化文件进行导出导入的事必躬亲了。详细介绍与应用,请参见联机丛书。

 

2.2 导入数据

运用BULK
INSERT把数量导入到指标表数据。为坚实质量,可有的时候删除索引,导完事后再重新构建索引等。请留心要留住丰硕的磁盘空间。这里差不离花了15秒钟导完。

997755.com澳门葡京 38

figure-11

 

 

3. 扩展

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

是因为专门的学问涉及,不常要付出一些客商的数目接口,每一天活动导入非常的大气的数额。限制于应用程序等因素影响,所以考虑直接利用SQL
SEMuranoVE奔驰G级的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普拉多VEOdyssey有权力读取相关目录和文书的权能。在Sql Server
Configuration Manager –> SQL Server Services
选用相应的实例,右键接纳属性,在Log On页签,使用有丰富权限运营SQL
SE哈弗VEENCORE和有权力读取相关目录的客商,比方读取互连网盘。

997755.com澳门葡京 39

figure-12

 

在SQL Server Agent新建三个功课

997755.com澳门葡京 40

figure-13

 

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

997755.com澳门葡京 41

figure-14

 

在Steps页,在Command里推行写好的贮存过程。

997755.com澳门葡京 42

figure-15

 

在Schedules页,配置实行的日子和功效等。完结。

997755.com澳门葡京 43

figure-16

 

 

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

相似的话,从低版本备份的数据库能够一贯在高版本的数据库中回复的,举个例子SQL两千的备份能够在SQL二〇〇六或SQL二零零六中回复,除非是跨度太大的之外。举例SQL三千的备份就不能够平昔在SQL二零一三中回复,只可以复苏到SQL二〇一〇,再从SQL二〇〇九备份出来,最终到SQL二零一二上回复。

而高版本的备份一般不可能在低版本中回复,如SQL二零零六的备份不能够在SQL2007或SQL3000中平复。而实际中,却又会遇见这种须求。最棒是经过高版本SSMS直接连接七个例外版本的数据库,通过数据库间的数码导出导入或写剧本,把高版本的数额导到低版本的数据库中。那是相比高效安全的措施。但是借使五个版本的数据库不财富源,只好是把数据导出来,再导入。对于数据量一点都不大的话,使用SSMS的导出导入功能,或是生成包涵数据的台本就能够(下图)。对于大数额来讲,却是多个不幸,如前方有3000万数据的大表,生成数据的剧本也会有多少个G大,间接利用SSMS试行是不容许的了。只可以是选用SQLCMD实用工具,在后台施行SQL脚本,只怕借助BCP、BULK
INSERT等这种大体量数据导出导入的工具。

997755.com澳门葡京 44

figure-17

 

4. 总结

应用BCP并结成BULK
INSERT可达成大容积数据的飞快导出导入,并能够完结其自动化专门的学问。对于少许数额的话,操作也不算很复杂。那是除了SSMS上的图形化学工业具之外,又三个要命实用的工具。

 

相关文章

发表评论

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

*
*
Website