SQLServer导数据到Oracle,BCP导出导入大容积数据推行

数据库 17
数据库
  • 字符编码
  • 字段分隔符
  • 行终止符
  • 日子或时刻格式
  • 特殊字符
  • 导入字段的种种
  • 导文件文件的表字段类型和长度是不是适用

figure-10

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.

默许下,生成的日志文件在当前目录下。无论成功与否,应当要翻看日志。看看是或不是导入成功或战败,或是部分成功。导入的难题日常从日记文件就可以找到。

相似的话,从低版本备份的数据库能够一向在高版本的数据库中还原的,比如SQL3000的备份能够在SQL二零零五或SQL二零零六中上升,除非是跨度太大的之外。比方SQL3000的备份就不能够直接在SQL2013中苏醒,只可以恢复生机到SQL二〇〇八,再从SQL贰零零捌备份出来,最后到SQL二〇一一上恢复生机。

外表表对错误的拍卖 

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”的日志文件,在那之中保存了失误的多少,用记事本张开看看这里出错,是还是不是留存于表面表定义相冲突

行使BCP合适导出大体积数据。这里导出千万级其余数额,也是便捷就能够得逞。

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

外界表定义的多少个基本点 

[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

 

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

 

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  
)

前言

创办外界表的注目事项 

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

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

a.查看表面表信息

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

 

使用Oracle的SQL*LOADE本田UR-V导入平面文件。即使Oracle中有已经创设好的表,与导入文本对应。

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

 

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

若是导出时还索要做一些多少的管理,举例多表关联,字符管理等,比较复杂的逻辑,最好是做成存款和储蓄进程,BCP直接调用存款和储蓄过程就能够。

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

1.外界表的创立语法

createtabletable_name
           (col1 datatype1,col2 datatype2,col3 datatype3)
            organization exteneral
           (…..)
详见语法可参见作者的另两篇文章

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

Oracle外部表ORACLE_LOADE昂科雷类型的开创语法详解:

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;

进程要花上几分钟的时光手艺形成,请耐心等待一下。关于数据的结构,可以参照小编的另一篇博文:

 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"

行使平面文件迁移数据,最大麻烦是正是特殊字符,或是有垃圾数据。借使原数据包蕴与字符分隔符相同的字符,如那之中的“||”,或是有一点点不可以知道的字符,如回车,换行符,等。这一个字符会变成导入时,分割字段错位,导致导入错误,数据导不全,以致导入退步。

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

g.创设健康的表,将表面表数据导入,那便是接纳ORACLE_DATAPUMP类型的额外界表实现数据迁移

create table tb1 as select * from in_tb1;

但从导出导入的速度来讲,是最快的,平面文件能够跨差别的数据库进行搬迁。如若数额不容忍错过,只好通过工具来导了,但速度会相对不快。

2.1 导出多少

2..尊崇参数外界表的门类

ORACLE_LOADE汉兰达:定义外界表的缺省格局,只好只读形式完成文件数据的装载。
ORACLE_DATAPUMP:协理对数码的装载与卸载,数据文件必需为二进制dump文件。能够从外表表提取数额装载到里头表,也足以从里头表卸载数据作为二进制文件填充到外界表。

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

code-4

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

今是昨非的操作系统对于外界表有例外的演讲和突显格局
如在Linux操作系统中制造的文书是分号分隔且每行一条记下,但该文件在Windows操作系统上张开则并不是这样。
建议防止不相同操作系统以致区别字符集所推动的影响

使用SQL*LOADE中华V注意多少个难题:

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

5.ACCESS PARAMETELX570S:描述怎么样对外表表张开寻访

RECOLX570DS关键字后定义如何识别数据行  
DELIMITED BY
‘XXX’——换行符,常用newline定义换行,并指明字符集。对于特别的字符则必要单独定义,如特殊符号,可以使用OX’14位值’,比方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,。
LRT昂科雷IM ——删除首尾空白字符。
MISSING FIELD VALUES ARE NULL——有个别字段空缺值都设为NULL。
对于字段长度和分割符不鲜明且策画作为外界表文件,能够利用UltraEdit、Editplus等来张开分析测量试验,若是文件异常的大,则须要思考将文件分割成小文件并从当中提取数额进行测验。

从SQLServer导数据到Oracle差不离有以下两种办法:

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.

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

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

 

3.DEFAULT DIRECTO翼虎Y:缺省的目录指明了表面文件所在的路线

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

figure-4

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

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

即使有错误,还大概会调换与导入文本同名的t1.bad文件。

利用BCP并结合BULK
INSERT可完毕大体积数据的高速导出导入,并能够达成其自动化专业。对于一丢丢数量的话,操作也不算很复杂。那是除了SSMS上的图形化学工业具之外,又二个要命实用的工具。

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.2 高版本数据库降级到低版本

4.去除却界表可能目录对象

平日意况下,先删除了那几个之外界表,然后再删除目录对象,若是目录对象中有多个表,应除去全体表之后再删除目录对象。
一旦在未删减外界表的动静下,强制删除了目录,在询问到被删除的外表表时,将吸收接纳”对象不设有”的错误信息。
查询dba_external_locations来取妥贴前享有的目录对象以致相关的外界表,同时会付给这一个外界表所对应的操作系统文件的名字。 假使只是在数据库层面上删除此之外界表,并不会活动删除操作系统上的外表表文件。

  1. 应用SSMS的导出数据向导,使用Microsoft ODBC for Oracle或Oracle
    Provider for OLE DB连接受Oracle
  2. 导出到平面文件
  3. 导出满含数据的SQL脚本。
  4. 使用ETL工具。
  5. 投机开拓软件。

SQL
SEXC90VE中华V提供多样差别的多少导出导入的工具,也得以编写制定SQL脚本,使用存款和储蓄进度,生成所需的数据文件,以致足以变动包蕴SQL语句和数目标脚本文件。各有利弊,以适用区别的供给。上面介绍大体积数据导出导入的利器——BCP实用工具。同有的时候间在后边也介绍BULK
INSERT导入大容积数据,以及BCP结合BULK
INSERT做多少接口的实践(在SQL二零一零本田UR-V2上实行)。

 4.外表表相关视图

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

 

 

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;

 

1.O中华VGANIZATION EXTE途锐NAL主要字,必供给有。以评释定义的表为外界表。

数据库 1

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

figure-13

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;

 

创建外界表 

应用CREATE TABLE语句的O奇骏GANIZATION
EXTENERAL子句来创立外界表。外界表不分配任何盘区,因为独有是在数码字典中开创元数据。

数据库 2

1.急需先创建目录对象

在成立指标的时候,须要小心,Oracle数据库系统不会去确认那些目录是或不是真正存在。假诺在输入那个目录对象的时候,相当的大心把门路写错了,那可能这几个外部表如故能够健康创设,不过却不可能查询到数量。由于建设构造目录对象时,缺少这种本人反省的建制,为此在将路线赋予给这几个目录对象时,要求特意的举世瞩目。别的要求留神的是门路的大小写。在Windows操作系统中,其路线是不区分轻重缓急写的。而在Linux操作系统,那些渠道必要区分轻重缓急写。故在差异的操作系统
中,建设构造目录对象时供给专心那么些尺寸写的反差

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

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.

 

表面表的风味 

坐落文件系统之中,按自然格式分割,如文本文件恐怕别的类型的表能够看作外界表。
对外界表的拜候能够透过SQL语句来达成,而无需先将表面表中的多寡装载进数据库中。
外表数据表都以只读的,因而在外界表不可以见到实施DML操作,也不能够成立索引。
ANALYZE语句不帮助收罗外界表的总计数据,应该运用DMBS_STATS包来搜集外界表的总括数据。

figure-15

3.在确立有的时候表时的相干限制

对表中字段的称号存在特殊字符的图景下,必须运用乌Crane语状态的下的双引号将该表列名称连接起来。如使用”SalseID#”。
对此列名字中特殊符号未使用双引号括起来时,会促成敬谢不敏平常查询数据。
提出不用接纳特其余列标题字符
在创设外界表的时候,并未有在数据库中创设表,也不会为外界表分配任何的存放空间。
创设外界表只是在数码字典中成立了外界表的元数据,以便对应访谈外界表中的数额,而不在数据库中存款和储蓄外界表的多少。
简单易行地说,数据仓库储存款和储蓄的只是与外界文件的一种对应提到,如字段与字段的对应关系。而未有存款和储蓄实际的数目。
是因为存款和储蓄实际数目,故不能为外界表制造索引,同期在数据运用DML时也不扶助对外界表的插入、更新、删除等操作。

数据库 3

外界表概述

表面表只可以在Oracle
9i之后来使用。轻便地说,外部表,是指官样文章于数据库中的表。通过向Oracle提供描述外界表的元数据,大家能够把三个操作系统文件正是贰个只读的数码库表,就如那一个多少存储在三个司空见惯数据库表中同样来开展访谈。外部表是对数据库表的延伸。

运用完事后,能够把sp_cmdshell关闭。

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;

code-1

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.

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

code-5

外表表的局限性 

1.SQLLD中华V得以钦赐多少提交三遍,即ROWS=?,
外部表却未有,那对于大数据量的导入有个别不方例。
数据库,2.sqlldr errors象征同意错误的行数,外界表用REJECT LIMIT
UNLIMITED,那几个功用上基本一样。
3.外界表的列不可能内定为not nullable,那样就很难拒绝某列为空值的记录。
4.外表表无法使用continueif ,若是记录有换行的就相比难管理。

 

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

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

营造外界表时,必需钦命操作系统文件所利用的相间符号。况兼该分隔符有且只有二个。成立外界表时,不能够含有标题列。假使这些标题信息与表面表的字段类型分裂等(如字段内容是number数据类型,而标题音信则是字符型数据,则在查询时就能够出错)。若是数据类型凑巧一致的话,那个标题音信Oracle数据库也会作为普通记录来比较。

当Oracle数据库系统访谈那些操作系统文件的时候,会在此个文件所在的目录自动创造贰个日记文件。无论最终是不是访谈成功,那些日志文件都会准时组建。查看这些日志文件,能够明白数据库访谈外部表的作用、是不是中标访问等等。暗中同意意况下,该日志在与表面表的等同directory下产生。

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

在SSMS上还要也足以施行:

从个人来说,作者更爱好使用第三种跟queryout挑选一齐使用的写法,因为这么能够进一步灵敏决定要导出的数额。假设施行BCP命令蒙受这么的谬误提醒:

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

figure-7

在Steps页,在Command里进行写好的蕴藏进程。

figure-16

 

 

 

figure-5

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

 

归纳的导出例子1:

figure-2

1. BCP的用法

 

数据库 4

figure-8

 

 

 

 

code-3

 

code-7

数据库 5

介绍完BCP的导出导入,以至BULK
INSERT的导入,下边进行局地实际的操作。为了接近实际条件,创立一张11个字段的表,包涵有二种常用的数据类型,构造三千万的数额,包罗中文和保加奇瓦瓦语。为了越来越快插入测量试验数据,先不创立索引。在奉行上面代码之前,请介怀下数据库的日记复苏形式是还是不是设置为大容积方式或简捷格局,乃至磁盘空间是还是不是丰硕(小编的实行中,数据变动后数据文件和日志文件大致须要40G的空中)。

修改figure-第22中学的out为in就能够,把数量导入。

数据库 6

 

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

 

数据库 7

 

figure-17

比较BCP的导入,BULK INSERT提供更加灵敏的挑肥拣瘦。

更详实的参数,请参见:

BCP导入数据

figure-11

第一要计划好的是SQL SESportageVEEscort有权力读取相关目录和文书的权位。在Sql Server
Configuration Manager –> SQL Server Services
选拔相应的实例,右键接纳属性,在Log On页签,使用有丰裕权限运维SQL
SEEscortVE帕Jero和有权力读取相关目录的顾客,譬喻读取互连网盘。

figure-1

运用BULK
INSERT把数量导入到目的表数据。为加强品质,可有时删除索引,导完事后再重新建立索引等。请留意要留下足够的磁盘空间。这里大约花了15分钟导完。

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO

在SQL Server Agent新建三个功课

 

运用BULK INSERT导入数据

3. 扩展

 

 

figure-9

 

 

 

在Schedules页,配置施行的年华和频率等。完成。

3.1.2 配置JOB

数据库 8

 

EXEC [master]..xp_cmdshell
'BCP TestDB_2005.dbo.T1 out E:\T1_02.txt -c -T'
GO

4. 总结

3.1.1 编写导入脚本

 

figure-14

figure-6

语法:

 

 

 

数据库 9

 

数据库 10

 

 

code-6

数据库 11

figure-3

听别人说安全的思考,系统暗中认可未有开启xp_cmdshell选项。使用上面语句开启此选项。

数据库 12

  • 具有不相同架构的几个表使用同一数据文件作为数据源。

  • 数据文件中的字段数分化于目的表中的列数;举例:

    • 对象表中最少含有三个概念了私下认可值或同意为 NULL 的列。

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

    • 持有差别框架结构的多少个或八个表使用同七个数据文件。

     

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

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

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

2.2 导入数据

code-2

简短的导出例子2:

 

figure-12

数据库 13

鉴于工作提到,有时要费用一些客商的数据接口,每日活动导入相比豁达的数量。限制于应用程序等因素影响,所以思念直接利用SQL
SE昂科拉VE奔驰M级的BULK
INSERT每一日活动去读取相关目录的中级文件。即便目录是动态的,但由于中等文件是固定格式的,通过编制动态SQL,最终封装成存款和储蓄进程,放到JOB中,配置运营的陈设,就可以成功自动化的干活。上面轻松演示下进程:

 

数据库 14

数据库 15

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

而高版本的备份常常不能够在低版本中平复,如SQL二零零六的备份无法在SQL2007或SQL3000中复苏。而实际中,却又会境遇这种供给。最棒是经过高版本SSMS直接连接七个不等版本的数据库,通过数据库间的数额导出导入或写剧本,把高版本的多少导到低版本的数据库中。那是比较神速安全的办法。不过要是三个版本的数据库不能够持续,只可以是把数据导出来,再导入。对于数据量非常小以来,使用SSMS的导出导入功效,或是生成包蕴数据的台本就可以(下图)。对于大数目标话,却是三个劫难,如前方有3000万数量的大表,生成数据的本子也可能有多少个G大,间接采取SSMS实践是不容许的了。只好是接纳SQLCMD实用工具,在后台执行SQL脚本,只怕依据BCP、BULK
INSERT等这种大体积数据导出导入的工具。

那边运用-w参数。BCP能够在CMD下导出多少,测量检验导出两千万条记下,小编的记录簿使用了近8分钟左右的日子。BCP同期也能够在SSMS中举行,使用了6分多钟时间,比CMD下速度要快些,生成的文件大小一致,每一个文件近5GB。

 

 

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 才能成功登录。

数据库 16

2. 实践

code-8

数据库 17

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

 

发表评论

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

网站地图xml地图