BCP导出导入大容量数据举行,SERVER两种多少迁移

从SQLServer导数据到Oracle大概有以下二种方法:

前言

BCP是SQL
Server中负责导入导出数据的一个命令行工具,它是基于DB-Library的,并且能以相互的艺术便捷地导入导出大批量的数目。BCP可以将数据库的表或视图直接导出,也能通过SELECT
FROM语句对表或视图举行过滤后导出。在导入导出数据时,可以动用默认值或是使用一个格式文件将文件中的数据导入到数据库或将数据库中的数据导出到文件中。

SQLServer提供了多种数量导出导入的工具和办法,在此,分享自己执行的阅历(只关乎数据库与Excel、数据库与公事文件、数据库与数据库之间的导出导入)。

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

SQL
SERVER提供多种不同的多少导出导入的工具,也足以编制SQL脚本,使用存储过程,生成所需的数据文件,甚至能够转移包含SQL语句和数据的台本文件。各有利弊,以适用不同的急需。下边介绍大容量数据导出导入的利器——BCP实用工具。同时在背后也介绍BULK
INSERT导入大容量数据,以及BCP结合BULK
INSERT做多少接口的实施(在SQL2008R2上实施)。

下边将详细谈论哪边行使BCP导入导出多少。

(一)数据库与Excel

以下使用第2种艺术来开展数量迁移的。

 

1. BCP的重要参数介绍

方法1:

行使BCP合适导出大容量数据。这里导出千万级另外数额,也是便捷就能得逞。

1. BCP的用法

BCP共有两个动作可以采取。
(1) 导入。
以此动作使用in命令完成,后边跟需要导入的公文名。
(2) 导出。
其一动作使用out命令完成,前边跟需要导出的文本名。
(3) 使用SQL语句导出。
其一动作使用queryout命令完成,它跟out类似,只是数据源不是表或视图名,而是SQL语句。
(4) 导出格式文件。
以此动作使用format命令完成,后而跟格式文件名。

接纳数据库客户端(SSMS)的界面工具。右键选取要导出数据的数据库,拔取“任务”——“导出数据”,下图1,遵照向导一步一步操作即可。而导入则相反,导入时,SQLServer会默认创造一张新表,字段名也默认跟导入的Excel标题一样,并且会默认字段数据类型等。当然在可以在辅导举办改动。需要留意的是假若标题不是英文而是普通话,默认创立字段名也是闽南语,这将给末端数据更新操作带来劳动,所以最好或者以有含义的英文字段名。把多少导入后,再通过履行语句,把数据插入/更新到业务表。

即使导出时还索要做一些数目标处理,比如多表关联,字符处理等,相比复杂的逻辑,最好是做成存储过程,BCP间接调用存储过程即可。

BCP 实用工具能够在 Microsoft SQL Server
实例和用户指定格式的数据文件间大容量复制数据。使用
BCP实用工具可以将大气新行导入 SQL Server
表,或将表数据导入数据文件。除非与 queryout
选项一起行使,否则使用该实用工具不需要掌握 Transact-SQL
知识。BCP既可以在CMD指示符下运行,也得以在SSMS下实施。

下面介绍部分常用的选项:

997755.com澳门葡京 1

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澳门葡京 2

-f format_file
format_file表示格式文件名。那些选项倚重于上述的动作,即使应用的是in或out,format_file表示曾经存在的格式文件,假如运用的是format则代表是要转移的格式文件。

 figure-1:任务——导出数据

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

figure-1

-x
以此选项要和-f format_file配合使用,以便生成xml格式的格式文件。

 

使用Oracle的SQL*LOADER导入平面文件。假使Oracle中有一度创办好的表,与导入文本对应。

 

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

方法2:

把以下的始末用vi,写到import-t1.ctl

语法:

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

从SQLServer2005先导,可以直接在SSMS上查询出来的结果复制,然后粘贴到Excel上,对于少量数量的话,是不行迅速方便的,需要专注的是长数字或者会化为科学记数法的样式,提前在Excel上点名列的格式为文本即可。

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

 

-c
采取char类型做为存储类型,没有前缀且以”\t”做为字段分割符,以”\n”做为行分割符。

导入的话,ctrl + c
复制Excel上的数额,然后在甄选有关表,编辑数据,把数据直接粘贴上去即可。可是不指出直接粘贴到业务表(假使表是空白没有多少,并且字段顺序对应,可以如此操作),而是提出先粘贴到一个新建的中等表中,然后再经过说话,把多少插入/更新到业务表。

使用SQL*LOADER注意多少个问题:

 

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

这种方法的导出导入,适合于少量的多少,如5000行以内的笔录,大于5000行以上就不提出了,速度较慢,假设数量过大,还一定成功。

  • 字符编码
  • 字段分隔符
  • 行终止符
  • 日子或时刻格式
  • 特殊字符
  • 导入字段的逐条
  • 导文件文件的表字段类型和长短是否确切

一句话来说的导出例子1:

-t field_term
指定字符分割符,默认是”\t”。

 

行使sqlldr命令把多少导入到Oracle中。

997755.com澳门葡京 3

-r row_term
点名行分割符,默认是”\n”。

 

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

figure-2

-S server_name[ \BCP导出导入大容量数据举行,SERVER两种多少迁移。instance_name]
指定要连续的SQL Server服务器的实例,倘若未指定此选项,BCP连接本机的SQL
Server默认实例。即便要连续某台机械上的默认实例,只需要指定机器名即可。

(二)数据库与公事文件、数据库与数据库

默认下,生成的日志文件在当前目录下。无论成功与否,一定要翻看日志。看看是否导入成功或战败,或是部分成功。导入的问题一般从日记文件即可找到。

 

-U login_id
指定连接SQL Sever的用户名。

数据库之间的数量迁移或导出导入其实是相比有利的,比如备份数据库后,在新的机械上做苏醒。但是需要小心的是SQL2008事先的本子的备份无法在SQL2012或以上版本上直接回复的,而是经过中间的SQL2008做一个联网,把旧版本的数据库恢复生机到SQL2008,然后做备份,最终在SQL2012上过来。

只要有荒唐,还会转变与导入文本同名的t1.bad文件。

简单易行的导出例子2:

-P password
指定连接SQL Server的用户名密码。

淌倘若新本子(下面以SQL2012为例)的备份文件復苏到旧版本(以SQL2008为例)上就相比较麻烦了,一般是不协理新本子备份文件在旧版本中回复的。只好通过编制脚本,把新本子的数码导入到旧版本中。

以下是日记文件,突显数据导入的一些信息。成功导入了18495032行记录,没有导入败北的记录。

997755.com澳门葡京 4

-T
点名BCP使用信任连接登录SQL Server。倘若未指定-T,必须指定-U和-P。

 

[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

-k
指定空列使用null值插入,而不是这列的默认值。

方法1:

利用平面文件迁移数据,最大麻烦是就是特殊字符,或是有破烂数据。假诺原数据包含与字符分隔符相同的字符,如这之中的“||”,或是有部分不可见的字符,如回车,换行符,等。这多少个字符会造成导入时,分割字段错位,导致导入错误,数据导不全,甚至导入失败。

 

2. 如何利用BCP导出多少

第一推荐使用的是数量不落地的“链接服务器”。使用SQL2012的SSMS,同时连接到SQL2012和SQL2008的实例,通过编制脚本把SQL2012的多少导入到SQL2008中。五个实例的可以透过链接服务器来连续。以下是设置步骤。

但从导出导入的进度来说,是最快的,平面文件可以跨不同的数据库举办搬迁。要是数量不容忍丢失,只可以通过工具来导了,但速度会相对较慢。

在SSMS上同时也得以举行:

(1) 使用BCP导出任何表或视图。

997755.com澳门葡京 5

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

BCP AdventureWorks.sales.currency out c:\currency1.txt -c -U”sa”
-P”password” –使用密码连接

figure-2:新建链接服务器

code-1

 

 

BCP AdventureWorks.sales.currency out c:\currency1.txt -c -T
–利用信任连接

997755.com澳门葡京 6

997755.com澳门葡京 7

上面是上述命令执行后的出口结果

figure-3:链接服务器和数据源

figure-4

Starting copy…
105 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 10 Average : (10500.00 rows per sec.)

 

 

上边是currency1.txt的部分内容

997755.com澳门葡京 8

 

AED Emirati Dirham 1998-06-01 00:00:00.000
AFA Afghani 1998-06-01 00:00:00.000
… … …
… … …
ZWD Zimbabwe Dollar 1998-06-01 00:00:00.000

figure-4:认证

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

在应用密码登录时需要将-U后的用户名和-P后的密码加上双引号。

 

code-2

:BCP除了可以在控制台执行外,仍可以够透过调用SQL
Server的一个系统存储过程xp_cmdshell以SQL语句的章程运行BCP。如上述第一条命令可改写为

997755.com澳门葡京 9

 

 

EXEC master..xp_cmdshell ‘BCP AdventureWorks.sales.currency out
c:\currency1.txt -c -U”sa” -P”password”‘

figure-5:创建成功后,可以直接浏览链接服务器的目录,也足以行使语句查询了。

997755.com澳门葡京 10

执行xp_cmdshell后,重回信息以表的形式出口。为了可以一本万利地在SQL中履行BCP,下面的指令都施用xp_cmdshell执行BCP命令。

 

figure-5

(2) 对要导出的表举办过滤。

也得以行使脚本来创立链接服务器。

 

BCP不仅可以接受表名或视图名做为参数,也足以承受SQL做为参数。通过SQL语句可以对要导出的表展开过滤,然后导出过滤后的笔录。

--创建链接服务器
EXEC sp_addlinkedserver 
@server='LINKED_SERVER_TEST2',--被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='192.168.88.6,11433'--数据源
GO

--创建登录名和密码
EXEC sys.sp_addlinkedsrvlogin
@rmtsrvname = 'LINKED_SERVER_TEST2', -- 被访问的服务器别名
@useself = 'false',
@locallogin = NULL,
@rmtuser = 'sa', -- 数据源登录名
@rmtpassword = 'psd123456' -- 数据源登录密码
GO

--设置数据可以访问
EXEC sys.sp_serveroption
@server = 'LINKED_SERVER_TEST2', 
@optname = 'data access',
@optvalue = N'true'
GO

从个体来讲,我更欣赏使用第二种跟queryout挑选一起使用的写法,因为这么可以进一步灵敏决定要导出的多寡。倘若实施BCP命令遭遇这么的一无是处指示:

EXEC master..xp_cmdshell ‘BCP “SELECT TOP 20 * FROM
AdventureWorks.sales.currency” queryout c:\currency2.txt -c -U”sa”
-P”password”‘

code-1:缔造链接服务器的台本

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.

BCP还是可以透过简单地设置选项对导出的行开展界定。

 

依照安全的考虑,系统默认没有拉开xp_cmdshell选项。使用下边语句开启此选项。

EXEC master..xp_cmdshell ‘BCP “SELECT TOP 20 * FROM
AdventureWorks.sales.currency” queryout c:\currency2.txt -F 10 -L 13 -c
-U”sa” -P”password”‘

创制成功后,可以直接询问数据。

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

这条命令使用了多少个参数-F 10和-L 13,表示从SELECT TOP 20 * FROM
AdventureWorks.sales.currency所查出来的结果中取第10条到13条记下举办导出。

997755.com澳门葡京 11

code-3

3. 什么采纳BCP导出格式文件

figure-6:查询链接服务器的多寡

 

BCP不仅可以按照表、视图导入导出多少,还足以配合格式文件对导入导出数据举行限定。格式文件以纯文本文件格局存在,分为一般格式和xml格式。用户可以手工编制格式文件,也得以由此BCP命令按照表、视图自动生成格式文件。

 

应用完之后,可以把sp_cmdshell关闭。

EXEC master..xp_cmdshell ‘BCP AdventureWorks.sales.currency format nul
-f c:\currency_format1.fmt -c -T’

由此视图sys.servers可以查询所有服务器及相关的属性。

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO

上述命令将currency表的协会变迁了一个格式文件currency_format1.fmt,下面是其一格式文件的始末。

997755.com澳门葡京 12

code-4

9.0
3
1 SQLCHAR 0 6 “\t” 1 CurrencyCode SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 “\t” 2 Name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 24 “\r\n” 3 ModifiedDate

figure-7:查询所有链接服务器

 

这么些格式文件记录了这么些表的字段(共3个字段)类型、长度、字符和行分割符和字段名等音讯。

 

BCP导入数据

BCP还足以经过-x选项生成xml格式的格式文件。

在SSMS上或运行以下脚本可以去除指定的链接服务器。

修改figure-2中的out为in即可,把数量导入。

EXEC master..xp_cmdshell ‘BCP AdventureWorks.sales.currency format nul
-f c:\currency_format2.fmt -x -c -T’

--删除链接服务器及所有登录
EXEC sys.sp_dropserver
 @server = 'LINKED_SERVER_TEST2',
 @droplogins = 'droplogins'
 GO

997755.com澳门葡京 13

xml格式文件所讲述的始末和普通格式文件所描述的内容完全平等,只是格式不同。

 code-2:删除链接服务器及具有登录

figure-6

4. 怎么行使BCP导入数据

 

 

BCP可以通过in命令将上边所导出的currency1.txt和currency2.txt再另行导入到数据库中,由于currency有主键,由此我们将复制一个和currency的布局完全平等的表。

详见请参考:

997755.com澳门葡京 14

SELECT TOP 0 * INTO AdventureWorks.sales.currency1 FROM
AdventureWorks.sales.currency

 

figure-7

将数据导入到currency1表中

 

 

EXEC master..xp_cmdshell ‘BCP AdventureWorks.sales.currency1 in
c:\currency1.txt -c -T’

方法2:

拔取BULK INSERT导入数据

导入数据也如出一辙可以利用-F和-L选项来抉择导入数据的记录行。

假若六个实例无法连续,只好在SQL2012上导出数据,再到SQL2008上导入。SQLServer提供生成包含数据的脚本工具,下图2。在第三步的“高级”选项里有一项“Types
of data to scripts”有多个挑选:Data only,Schema and data,Schema
only,分别是只生成数据、生成表(对象)和多少,表(对象)。还有生成脚本的本子“Script
for Server Version”,下图3。其他选拔,按实际需要拔取。

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

EXEC master..xp_cmdshell ‘BCP AdventureWorks.sales.currency1 in
c:\currency1.txt -c -F 10 -L 13 -T’

 

code-5

在导入数据时得以按照已经存在的格式文件将满意条件的笔录导入到数据库中,不满意则不导入。如上述的格式文件中的第多少个字段的字符长度是24,假如某个文本文件中的相应字段的长短超越24,则这条记下将不被导入到数据库中,其余满意条件的记录健康导入。

997755.com澳门葡京 15

 

动用普通的格式文件

 figure-8:任务——生成脚本

997755.com澳门葡京 16

EXEC master..xp_cmdshell ‘BCP AdventureWorks.sales.currency1 in
c:\currency1.txt -F 10 -L 13 -c -f c:\currency_format1.fmt -T’

 

figure-8

应用xml格式的格式文件

997755.com澳门葡京 17

 

EXEC master..xp_cmdshell ‘BCP AdventureWorks.sales.currency1 in
c:\currency1.txt -F 10 -L 13 -c -x -f c:\currency_format2.fmt -T’

figure-9:生成脚本的尖端选项

至于BULK
INSERT更详细的表达,参考:

总结

 

相相比较BCP的导入,BULK INSERT提供更灵活的挑三拣四。

BCP命令是SQL
Server提供的一个连忙的多寡导入导出工具。使用它不需要启动任何图形管理工具就能以连忙的办法导入导出数据。当然,它也可以经过xp_cmdshell在SQL语句中施行,通过这种措施可以将其置于客户端程序中(如delphi、c#等)运行,这也是使客户端程序具有数据导入导出效用的模式之一。

也可以动用存储过程生成包含数据的剧本。这里介绍一个旁人已经做写好存储过程:sp_generate_inserts。运行之后,会按表每条记录生成一条insert的讲话

 

997755.com澳门葡京 18997755.com澳门葡京 19

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

CREATE PROC [dbo].[sp_generate_inserts]
    (
      @table_name VARCHAR(776) ,          -- The table/view for which the INSERT statements will be generated using the existing data
      @target_table VARCHAR(776) = NULL ,     -- Use this parameter to specify a different table name into which the data will be inserted
      @include_column_list BIT = 1 ,        -- Use this parameter to include/ommit column list in the generated INSERT statement
      @from VARCHAR(800) = NULL ,         -- Use this parameter to filter the rows based on a filter condition (using WHERE)
      @include_timestamp BIT = 0 ,         -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement
      @debug_mode BIT = 0 ,            -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
      @owner VARCHAR(64) = NULL ,        -- Use this parameter if you are not the owner of the table
      @ommit_images BIT = 0 ,            -- Use this parameter to generate INSERT statements by omitting the 'image' columns
      @ommit_identity BIT = 1 ,        -- Use this parameter to ommit the identity columns
      @top INT = NULL ,            -- Use this parameter to generate INSERT statements only for the TOP n rows
      @cols_to_include VARCHAR(8000) = NULL ,    -- List of columns to be included in the INSERT statement
      @cols_to_exclude VARCHAR(8000) = NULL ,    -- List of columns to be excluded from the INSERT statement
      @disable_constraints BIT = 0 ,        -- When 1, disables foreign key constraints and enables them after the INSERT statements
      @ommit_computed_cols BIT = 1        -- When 1, computed columns will not be included in the INSERT statement

    )
AS 
    BEGIN

/***********************************************************************************************************
Procedure:    sp_generate_inserts  (Build 22) 
        (Copyright ?2002 Narayana Vyas Kondreddi. All rights reserved.)

Purpose:    To generate INSERT statements from existing data. 
        These INSERTS can be executed to regenerate the data at some other location.
        This procedure is also useful to create a database setup, where in you can 
        script your data along with your table definitions.

Written by:    Narayana Vyas Kondreddi
            http://vyaskn.tripod.com

Acknowledgements:
        Divya Kalra    -- For beta testing
        Mark Charsley    -- For reporting a problem with scripting uniqueidentifier columns with NULL values
        Artur Zeygman    -- For helping me simplify a bit of code for handling non-dbo owned tables
        Joris Laperre   -- For reporting a regression bug in handling text/ntext columns

Tested on:     SQL Server 7.0 and SQL Server 2000

Date created:    January 17th 2001 21:52 GMT

Date modified:    May 1st 2002 19:50 GMT

Email:         vyaskn@hotmail.com

NOTE:        This procedure may not work with tables with too many columns.
        Results can be unpredictable with huge text columns or SQL Server 2000's sql_variant data types
        Whenever possible, Use @include_column_list parameter to ommit column list in the INSERT statement, for better results
        IMPORTANT: This procedure is not tested with internation data (Extended characters or Unicode). If needed
        you might want to convert the datatypes of character variables in this procedure to their respective unicode counterparts
        like nchar and nvarchar


Example 1:    To generate INSERT statements for table 'titles':

        EXEC sp_generate_inserts 'titles'

Example 2:     To ommit the column list in the INSERT statement: (Column list is included by default)
        IMPORTANT: If you have too many columns, you are advised to ommit column list, as shown below,
        to avoid erroneous results

        EXEC sp_generate_inserts 'titles', @include_column_list = 0

Example 3:    To generate INSERT statements for 'titlesCopy' table from 'titles' table:

        EXEC sp_generate_inserts 'titles', 'titlesCopy'

Example 4:    To generate INSERT statements for 'titles' table for only those titles 
        which contain the word 'Computer' in them:
        NOTE: Do not complicate the FROM or WHERE clause here. It's assumed that you are good with T-SQL if you are using this parameter

        EXEC sp_generate_inserts 'titles', @from = "from titles where title like '%Computer%'"

Example 5:     To specify that you want to include TIMESTAMP column's data as well in the INSERT statement:
        (By default TIMESTAMP column's data is not scripted)

        EXEC sp_generate_inserts 'titles', @include_timestamp = 1

Example 6:    To print the debug information:

        EXEC sp_generate_inserts 'titles', @debug_mode = 1

Example 7:     If you are not the owner of the table, use @owner parameter to specify the owner name
        To use this option, you must have SELECT permissions on that table

        EXEC sp_generate_inserts Nickstable, @owner = 'Nick'

Example 8:     To generate INSERT statements for the rest of the columns excluding images
        When using this otion, DO NOT set @include_column_list parameter to 0.

        EXEC sp_generate_inserts imgtable, @ommit_images = 1

Example 9:     To generate INSERT statements excluding (ommiting) IDENTITY columns:
        (By default IDENTITY columns are included in the INSERT statement)

        EXEC sp_generate_inserts mytable, @ommit_identity = 1

Example 10:     To generate INSERT statements for the TOP 10 rows in the table:

        EXEC sp_generate_inserts mytable, @top = 10

Example 11:     To generate INSERT statements with only those columns you want:

        EXEC sp_generate_inserts titles, @cols_to_include = "'title','title_id','au_id'"

Example 12:     To generate INSERT statements by omitting certain columns:

        EXEC sp_generate_inserts titles, @cols_to_exclude = "'title','title_id','au_id'"

Example 13:    To avoid checking the foreign key constraints while loading data with INSERT statements:

        EXEC sp_generate_inserts titles, @disable_constraints = 1

Example 14:     To exclude computed columns from the INSERT statement:
        EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1
***********************************************************************************************************/

        SET NOCOUNT ON

--Making sure user only uses either @cols_to_include or @cols_to_exclude
        IF ( ( @cols_to_include IS NOT NULL )
             AND ( @cols_to_exclude IS NOT NULL )
           ) 
            BEGIN
                RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1)
                RETURN -1 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified
            END

--Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format
        IF ( ( @cols_to_include IS NOT NULL )
             AND ( PATINDEX('''%''', @cols_to_include) = 0 )
           ) 
            BEGIN
                RAISERROR('Invalid use of @cols_to_include property',16,1)
                PRINT 'Specify column names surrounded by single quotes and separated by commas'
                PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',''title''"'
                RETURN -1 --Failure. Reason: Invalid use of @cols_to_include property
            END

        IF ( ( @cols_to_exclude IS NOT NULL )
             AND ( PATINDEX('''%''', @cols_to_exclude) = 0 )
           ) 
            BEGIN
                RAISERROR('Invalid use of @cols_to_exclude property',16,1)
                PRINT 'Specify column names surrounded by single quotes and separated by commas'
                PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'',''title''"'
                RETURN -1 --Failure. Reason: Invalid use of @cols_to_exclude property
            END


--Checking to see if the database name is specified along wih the table name
--Your database context should be local to the table for which you want to generate INSERT statements
--specifying the database name is not allowed
        IF ( PARSENAME(@table_name, 3) ) IS NOT NULL 
            BEGIN
                RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1)
                RETURN -1 --Failure. Reason: Database name is specified along with the table name, which is not allowed
            END

--Checking for the existence of 'user table' or 'view'
--This procedure is not written to work on system tables
--To script the data in system tables, just create a view on the system tables and script the view instead

        IF @owner IS NULL 
            BEGIN
                IF ( ( OBJECT_ID(@table_name, 'U') IS NULL )
                     AND ( OBJECT_ID(@table_name, 'V') IS NULL )
                   ) 
                    BEGIN
                        RAISERROR('User table or view not found.',16,1)
                        PRINT 'You may see this error, if you are not the owner of this table or view. In that case use @owner parameter to specify the owner name.'
                        PRINT 'Make sure you have SELECT permission on that table or view.'
                        RETURN -1 --Failure. Reason: There is no user table or view with this name
                    END
            END
        ELSE 
            BEGIN
                IF NOT EXISTS ( SELECT  1
                                FROM    INFORMATION_SCHEMA.TABLES
                                WHERE   TABLE_NAME = @table_name
                                        AND ( TABLE_TYPE = 'BASE TABLE'
                                              OR TABLE_TYPE = 'VIEW'
                                            )
                                        AND TABLE_SCHEMA = @owner ) 
                    BEGIN
                        RAISERROR('User table or view not found.',16,1)
                        PRINT 'You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.'
                        PRINT 'Make sure you have SELECT permission on that table or view.'
                        RETURN -1 --Failure. Reason: There is no user table or view with this name        
                    END
            END

--Variable declarations
        DECLARE @Column_ID INT ,
            @Column_List NVARCHAR(MAX) ,
            @Column_Name VARCHAR(128) ,
            @Start_Insert NVARCHAR(MAX) ,
            @Data_Type VARCHAR(128) ,
            @Actual_Values NVARCHAR(MAX) ,    --This is the string that will be finally executed to generate INSERT statements
            @IDN VARCHAR(128)        --Will contain the IDENTITY column's name in the table

--Variable Initialization
        SET @IDN = ''
        SET @Column_ID = 0
        SET @Column_Name = ''
        SET @Column_List = ''
        SET @Actual_Values = ''

        IF @owner IS NULL 
            BEGIN
                SET @Start_Insert = 'INSERT INTO ' + '['
                    + RTRIM(COALESCE(@target_table, @table_name)) + ']' 
            END
        ELSE 
            BEGIN
                SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner))
                    + '].' + '[' + RTRIM(COALESCE(@target_table, @table_name))
                    + ']'         
            END


--To get the first column's ID

        SELECT  @Column_ID = MIN(ORDINAL_POSITION)
        FROM    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
        WHERE   TABLE_NAME = @table_name
                AND ( @owner IS NULL
                      OR TABLE_SCHEMA = @owner
                    )


--Loop through all the columns of the table, to get the column names and their data types
        WHILE @Column_ID IS NOT NULL 
            BEGIN
                SELECT  @Column_Name = QUOTENAME(COLUMN_NAME) ,
                        @Data_Type = DATA_TYPE
                FROM    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
                WHERE   ORDINAL_POSITION = @Column_ID
                        AND TABLE_NAME = @table_name
                        AND ( @owner IS NULL
                              OR TABLE_SCHEMA = @owner
                            )



                IF @cols_to_include IS NOT NULL --Selecting only user specified columns
                    BEGIN
                        IF CHARINDEX('''' + SUBSTRING(@Column_Name, 2,
                                                      LEN(@Column_Name) - 2)
                                     + '''', @cols_to_include) = 0 
                            BEGIN
                                GOTO SKIP_LOOP
                            END
                    END

                IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns
                    BEGIN
                        IF CHARINDEX('''' + SUBSTRING(@Column_Name, 2,
                                                      LEN(@Column_Name) - 2)
                                     + '''', @cols_to_exclude) <> 0 
                            BEGIN
                                GOTO SKIP_LOOP
                            END
                    END

        --Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
                IF ( SELECT COLUMNPROPERTY(OBJECT_ID(QUOTENAME(COALESCE(@owner,
                                                              USER_NAME()))
                                                     + '.' + @table_name),
                                           SUBSTRING(@Column_Name, 2,
                                                     LEN(@Column_Name) - 2),
                                           'IsIdentity')
                   ) = 1 
                    BEGIN
                        IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column
                            SET @IDN = @Column_Name
                        ELSE 
                            GOTO SKIP_LOOP            
                    END

        --Making sure whether to output computed columns or not
                IF @ommit_computed_cols = 1 
                    BEGIN
                        IF ( SELECT COLUMNPROPERTY(OBJECT_ID(QUOTENAME(COALESCE(@owner,
                                                              USER_NAME()))
                                                             + '.'
                                                             + @table_name),
                                                   SUBSTRING(@Column_Name, 2,
                                                             LEN(@Column_Name)
                                                             - 2),
                                                   'IsComputed')
                           ) = 1 
                            BEGIN
                                GOTO SKIP_LOOP                    
                            END
                    END

        --Tables with columns of IMAGE data type are not supported for obvious reasons
                IF ( @Data_Type IN ( 'image' ) ) 
                    BEGIN
                        IF ( @ommit_images = 0 ) 
                            BEGIN
                                RAISERROR('Tables with image columns are not supported.',16,1)
                                PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.'
                                PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit column list using @include_column_list=0, the generated INSERTs will fail.'
                                RETURN -1 --Failure. Reason: There is a column with image data type
                            END
                        ELSE 
                            BEGIN
                                GOTO SKIP_LOOP
                            END
                    END

        --Determining the data type of the column and depending on the data type, the VALUES part of
        --the INSERT statement is generated. Care is taken to handle columns with NULL values. Also
        --making sure, not to lose any data from flot, real, money, smallmomey, datetime columns
                SET @Actual_Values = @Actual_Values
                    + CASE WHEN @Data_Type IN ( 'char', 'varchar', 'nchar','nvarchar' )
                           THEN 'COALESCE(''N'''''' + REPLACE(RTRIM('
                                + @Column_Name
                                + '),'''''''','''''''''''')+'''''''',''NULL'')'
                           WHEN @Data_Type IN ( 'datetime', 'smalldatetime',
                                                'DATE','time' )
                           THEN 'COALESCE('''''''' + RTRIM(CONVERT(char,'
                                + @Column_Name + ',120))+'''''''',''NULL'')'
                           WHEN @Data_Type IN ( 'uniqueidentifier' )
                           THEN 'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM('
                                + @Column_Name
                                + ')),'''''''','''''''''''')+'''''''',''NULL'')'
                           WHEN @Data_Type IN ( 'text', 'ntext' )
                           THEN 'COALESCE(''N'''''' + REPLACE(CONVERT(char(8000),'
                                + @Column_Name
                                + '),'''''''','''''''''''')+'''''''',''NULL'')'
                           WHEN @Data_Type IN ( 'binary', 'varbinary' )
                           THEN 'COALESCE(RTRIM(CONVERT(char,'
                                + 'CONVERT(int,' + @Column_Name
                                + '))),''NULL'')'
                           WHEN @Data_Type IN ( 'timestamp', 'rowversion' )
                           THEN CASE WHEN @include_timestamp = 0
                                     THEN '''DEFAULT'''
                                     ELSE 'COALESCE(RTRIM(CONVERT(char,'
                                          + 'CONVERT(int,' + @Column_Name
                                          + '))),''NULL'')'
                                END
                           WHEN @Data_Type IN ( 'hierarchyid' )
                           THEN 'COALESCE(''CAST(''''''+LTRIM(RTRIM('
                                + 'CONVERT(char, ' + @Column_Name + ')'
                                + ')),''NULL'')' + '+''''''AS  hierarchyid)'''
                           WHEN @Data_Type IN ( 'float', 'real', 'money',
                                                'smallmoney' )
                           THEN 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, '
                                + @Column_Name + ',2)' + ')),''NULL'')'
                           ELSE 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, '
                                + @Column_Name + ')' + ')),''NULL'')'
                      END + '+' + ''',''' + ' + '


        --Generating the column list for the INSERT statement
                SET @Column_List = @Column_List + @Column_Name + ','    

                SKIP_LOOP: --The label used in GOTO

                SELECT  @Column_ID = MIN(ORDINAL_POSITION)
                FROM    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
                WHERE   TABLE_NAME = @table_name
                        AND ORDINAL_POSITION > @Column_ID
                        AND ( @owner IS NULL
                              OR TABLE_SCHEMA = @owner
                            )


    --Loop ends here!
            END

--To get rid of the extra characters that got concatenated during the last run through the loop
        SET @Column_List = LEFT(@Column_List, LEN(@Column_List) - 1)
        SET @Actual_Values = LEFT(@Actual_Values, LEN(@Actual_Values) - 6)

        IF LTRIM(@Column_List) = '' 
            BEGIN
                RAISERROR('No columns to select. There should at least be one column to generate the output',16,1)
                RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter
            END

--Forming the final string that will be executed, to output the INSERT statements
        IF ( @include_column_list <> 0 ) 
            BEGIN
                SET @Actual_Values = 'SELECT ' + CASE WHEN @top IS NULL
                                                           OR @top < 0 THEN ''
                                                      ELSE ' TOP '
                                                           + LTRIM(STR(@top))
                                                           + ' '
                                                 END + ''''
                    + RTRIM(@Start_Insert) + ' ''+' + '''('
                    + RTRIM(@Column_List) + '''+' + ''')'''
                    + ' +''VALUES(''+ ' + @Actual_Values + '+'')''' + ' '
                    + COALESCE(@from,
                               ' FROM ' + CASE WHEN @owner IS NULL THEN ''
                                               ELSE '[' + LTRIM(RTRIM(@owner))
                                                    + '].'
                                          END + '[' + RTRIM(@table_name) + ']'
                               + '(NOLOCK)')
            END
        ELSE 
            IF ( @include_column_list = 0 ) 
                BEGIN
                    SET @Actual_Values = 'SELECT '
                        + CASE WHEN @top IS NULL
                                    OR @top < 0 THEN ''
                               ELSE ' TOP ' + LTRIM(STR(@top)) + ' '
                          END + '''' + RTRIM(@Start_Insert)
                        + ' '' +''VALUES(''+ ' + @Actual_Values + '+'')'''
                        + ' ' + COALESCE(@from,
                                         ' FROM '
                                         + CASE WHEN @owner IS NULL THEN ''
                                                ELSE '[' + LTRIM(RTRIM(@owner))
                                                     + '].'
                                           END + '[' + RTRIM(@table_name)
                                         + ']' + '(NOLOCK)')
                END    

--Determining whether to ouput any debug information
        IF @debug_mode = 1 
            BEGIN
                PRINT '/*****START OF DEBUG INFORMATION*****'
                PRINT 'Beginning of the INSERT statement:'
                PRINT @Start_Insert
                PRINT ''
                PRINT 'The column list:'
        --PRINT @Column_List
                PRINT ''
                PRINT 'The SELECT statement executed to generate the INSERTs'
                PRINT @Actual_Values
                PRINT ''
                PRINT '*****END OF DEBUG INFORMATION*****/'
                PRINT ''
            END

        --PRINT '--INSERTs generated by ''sp_generate_inserts'' stored procedure written by Vyas'
        --PRINT '--Build number: 22'
        --PRINT '--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com'
        --PRINT '--http://vyaskn.tripod.com'
        --PRINT ''
        --PRINT 'SET NOCOUNT ON'
        --PRINT ''


--Determining whether to print IDENTITY_INSERT or not
        IF ( @IDN <> '' ) 
            BEGIN
                PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,
                                                              USER_NAME()))
                    + '.' + QUOTENAME(@table_name) + ' ON'
                PRINT 'GO'
                PRINT ''
            END


        IF @disable_constraints = 1
            AND ( OBJECT_ID(QUOTENAME(COALESCE(@owner, USER_NAME())) + '.'
                            + @table_name, 'U') IS NOT NULL ) 
            BEGIN
                IF @owner IS NULL 
                    BEGIN
                        SELECT  'ALTER TABLE '
                                + QUOTENAME(COALESCE(@target_table,
                                                     @table_name))
                                + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
                    END
                ELSE 
                    BEGIN
                        SELECT  'ALTER TABLE ' + QUOTENAME(@owner) + '.'
                                + QUOTENAME(COALESCE(@target_table,
                                                     @table_name))
                                + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
                    END

                PRINT 'GO'
            END

        PRINT ''
        PRINT 'PRINT ''Inserting values into ' + '['
            + RTRIM(COALESCE(@target_table, @table_name)) + ']' + ''''


--All the hard work pays off here!!! You'll get your INSERT statements, when the next line executes!
        EXEC (@Actual_Values)

        PRINT 'PRINT ''Done'''
        PRINT ''


        IF @disable_constraints = 1
            AND ( OBJECT_ID(QUOTENAME(COALESCE(@owner, USER_NAME())) + '.'
                            + @table_name, 'U') IS NOT NULL ) 
            BEGIN
                IF @owner IS NULL 
                    BEGIN
                        SELECT  'ALTER TABLE '
                                + QUOTENAME(COALESCE(@target_table,
                                                     @table_name))
                                + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'
                    END
                ELSE 
                    BEGIN
                        SELECT  'ALTER TABLE ' + QUOTENAME(@owner) + '.'
                                + QUOTENAME(COALESCE(@target_table,
                                                     @table_name))
                                + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'
                    END

                PRINT 'GO'
            END

        PRINT ''
        IF ( @IDN <> '' ) 
            BEGIN
                PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,
                                                              USER_NAME()))
                    + '.' + QUOTENAME(@table_name) + ' OFF'
                PRINT 'GO'
            END

        --PRINT 'SET NOCOUNT OFF'


        SET NOCOUNT OFF
        RETURN 0 --Success. We are done!
    END
GO
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 才能成功登录。

View Code

更详尽的参数,请参考:

code-3:sp_generate_inserts脚本源代码

 

 

 

在自家的实际利用中,唯有两多个参数相比常用,分别是@table_name、@from和@owner,假若表的架构使用默认的dbo,则足以概括。以下是一个施用的例子:

 

997755.com澳门葡京 20

2. 实践

figure-10:使用sp_generate_inserts的一个事例

2.1 导出多少

 

介绍完BCP的导出导入,以及BULK
INSERT的导入,下面举办局部实际的操作。为了好像实际条件,成立一张10个字段的表,包含有三种常用的数据类型,构造2000万的数据,包含中文和英文。为了更快插入测试数据,先不创立索引。在执行上边代码此前,请小心下数据库的日志復苏情势是否设置为大容量情势或简捷情势,以及磁盘空间是否充足(我的施行中,数据变动后数据文件和日志文件大概需要40G的空间)。

任何参数的用法,这里就不一一解释了。我不时利用这么些蕴藏过程做一些大概而微量(如数万行记录以内)的数额导出导入,比后边介绍的措施方便快捷许多。但以此蕴藏过程协助处理一般常用的数据类型,像XML这类别型则不协理。还有,假如生成的数量太多太大,SSMS再次来到数据会很慢,甚至SSMS会挂了,那时如故利用SSMS自带的导出脚本到文件稳妥些。如若利用生成的多少脚本文件很大,几百MB甚至上GB,在导入时,就不可能直接动用SSMS直接打开来执行了。可以利用SQLCMD实用工具来在举办脚本。如下边的一个例子,在D盘下有一个剧本1.sql,内容为:

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
USE AdventureWorks2008R2
GO
SELECT * FROM Person.CountryRegion;
GO

code-6

code-4:SQLMCD的测试脚本

 

 

进程要花上几分钟的日子才能不负众望,请耐心等待一下。关于数据的布局,可以参照我的另一篇博文:

在运转下输入CMD,输入:

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

sqlcmd -S localhost -d AdventureWorks2008R2 -i D:\1.sql
EXEC [master]..xp_cmdshell
'BCP AdventureWorks2008R2.dbo.T1 out E:\T1_04.txt -w -T -S KEN\SQLSERVER08R2'
GO

code-5:SQLMCD的命令

code-7

 

 

回车举行后如下图,SQLCMD的事无巨细用法,请参考:

这边运用-w参数。BCP可以在CMD下导出多少,测试导出2000万条记下,我的记录簿使用了近8分钟左右的年月。BCP同时也得以在SSMS中施行,使用了6分多钟时间,比CMD下速度要快些,生成的文件大小一致,每个文件近5GB。

997755.com澳门葡京 21

 

figure-9

997755.com澳门葡京 22

 

 figure-11:SQLCMD的测试例子

997755.com澳门葡京 23

 

figure-10

 

 

方法3:

而对此复杂的大容量导入情况,日常都会需要格式化文件。在偏下境况下,必须利用格式化文件:

行使BCP导出导入大容量数据。可以参见我的另一篇博客《BCP导出导入大容量数据进行》。

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

  • 数据文件中的字段数不同于目的表中的列数;例如:

    • 目的表中至少含有一个概念了默认值或允许为 NULL 的列。

    • 用户不享有对目标表的一个或六个列的 SELECT/INSERT 权限。

    • 装有不同架构的多个或六个表使用同一个数据文件。

     

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

  • 数据文件列的平息字符或前缀长度不同。

 

 

上述二种办法是自我在普通工作相比常动用的多少导出导入的工具,每一种形式都有分另外优势和不同的使用状况,使用不同的点子结合,可以节约成千上万时刻,进步工作效能,希望对您的兼具援助。假诺您有更好的提议或方法欢迎告诉我!

此地不采用格式化文件举办导出导入的示范了。详细介绍与行使,请参考联机丛书。

 

 

2.2 导入数据

行使BULK
INSERT把多少导入到目的表数据。为增长性能,可临时删除索引,导完将来再重建索引等。请留意要留住充分的磁盘空间。这里大约花了15分钟导完。

997755.com澳门葡京 24

figure-11

 

 

3. 扩展

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

鉴于工作关系,有时要支付一些客户的多少接口,天天活动导入相比豁达的多寡。限制于应用程序等因素影响,所以考虑直接利用SQL
SERVER的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 SERVER有权力读取相关目录和文书的权能。在Sql Server
Configuration Manager –> SQL Server 瑟维斯(Service)(Service)s
采纳相应的实例,右键选取属性,在Log On页签,使用有充裕权限启动SQL
SERVER和有权力读取相关目录的用户,比如读取网络盘。

997755.com澳门葡京 25

figure-12

 

在SQL Server Agent新建一个功课

997755.com澳门葡京 26

figure-13

 

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

997755.com澳门葡京 27

figure-14

 

在Steps页,在Command里实施写好的积存过程。

997755.com澳门葡京 28

figure-15

 

在Schedules页,配置执行的时刻和频率等。完成。

997755.com澳门葡京 29

figure-16

 

 

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

相似的话,从低版本备份的数据库可以一贯在高版本的数据库中还原的,比如SQL2000的备份可以在SQL2005或SQL2008中重操旧业,除非是跨度太大的之外。比如SQL2000的备份就不可以直接在SQL2012中还原,只好复苏到SQL2008,再从SQL2008备份出来,最后到SQL2012上过来。

997755.com澳门葡京,而高版本的备份一般无法在低版本中还原,如SQL2008的备份不可以在SQL2005或SQL2000中回复。而实际上中,却又会赶上这种需求。最好是经过高版本SSMS直接连接五个不同版本的数据库,通过数据库间的多寡导出导入或写剧本,把高版本的数据导到低版本的数据库中。这是相比急速安全的形式。但是假诺七个版本的数据库无法持续,只可以是把数据导出来,再导入。对于数据量不大的话,使用SSMS的导出导入功能,或是生成包含数据的脚本即可(下图)。对于大数据的话,却是一个不幸,如前方有2000万多少的大表,生成数据的脚本也有多少个G大,直接利用SSMS执行是不可能的了。只好是运用SQLCMD实用工具,在后台执行SQL脚本,或者借助BCP、BULK
INSERT等这种大容量数据导出导入的工具。

997755.com澳门葡京 30

figure-17

 

4. 总结

利用BCP并结成BULK
INSERT可实现大容量数据的神速导出导入,并可以实现其自动化工作。对于少量多少来说,操作也不算很复杂。这是除了SSMS上的图形化工具之外,又一个不行实用的工具。

 

相关文章

发表评论

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

*
*
Website