【关于数据大容量的导入导出小结】

?

?

?

1。关于大容量数据导入导出的一些方法

SQL?SERVER提供多种工具用于各种数据源的数据导入导出，这些数据源包括本文文件、ODBC数据源、OLE?DB数据源、ASCII文本文件和EXCEL电子表格。

?

2.常用工具

DTS:数据转换服务导入导出向导或者DTS设计器创建DTS包

使用SQL?SERVER复制发布数据

BCP命令提示实用工具实现SQL?SERVER实例和数据文件之间的数据导入导出

BULK?INSERT实现从数据文件导入数据到SQL?SERVER实例

分布式查询实现从一个数据源选择数据插入到SQL?SERVER实例

SELECT?INTO?语句插入数据表

?

?

3.导入导出的数据

1。导入数据的目标表必须存在。导出数据的目标文件如果存在，则将重写上面的内容。如果不存在，则BCP自动创建文件

2。数据文件中的数据必须是字符格式或是先前由bcp工具生成的格式(本机格式)

3。必须对相应的表拥有足够的权限

4。数据导入导出工具的简单用法

?

?

4.数据导入导出工具的简单用法

a.DTS

DTS是一组图形工具和可编程对象，是开发者可以将取自完全的不同源的数据析取、转换并合并成一个或者多个。

它的特点就是可以融合完全不同源的数据源?这在企业改进中应用很大?。

这里涉及到一个DTS包，它是一个有组织的链接、DTS任务、DTS转换和工作流约束的集合。

关于DTS的操作请参看相关具体文献。

?

b.BCP

它常用于将大量的数据从另外的程序转移到SQL?SERVER表中。当然也可以用于将表中数据传输到数据文件中。

下面是一些BCP的简单用法(关于很多的选项使用看相关文档)??

--前序,开启xp_cmdshell?

--关于xp_cmdshell的一些知识请看http://blog.csdn.net/feixianxxx/archive/2009/08/14/4445603.aspx

EXEC?sp_configure?'show?advanced?options',?1;RECONFIGURE;

EXEC?sp_configure?'xp_cmdshell',?1;RECONFIGURE;

--环境

create?table?test

(

id?int,

value?varchar(100)

)

go

insert?test?values(1,'s1')

insert?test?values(2,'s2')

insert?test?values(3,'s3')

insert?test?values(4,'s4')

go

--1将表的数据导出到TEXT.txt文件中

exec?master..xp_cmdshell?'bcp?tempdb.dbo.test?out?e:/test.txt?-c??-Usa?-P123456'

--如果是WINDOWS身份直接xec?master..xp_cmdshell?'bcp?tempdb.dbo.test?out?e:/test.txt?-T?-c'

--2将TEXT.txt文件中的数据复制到test1表

select?*?into?test1?from?test?where?1=2

exec?master..xp_cmdshell?'bcp?tempdb.dbo.test1?in?e:/test.txt?-c??-Usa?-P123456'

select?*?from?test1

--3将TEST表的ID字段复制到TEXT.txt中

exec?master..xp_cmdshell?'bcp?"SELECT?id?FROM?tempdb.dbo.test"?queryout?e:/test.dat?-T?-c'

--4将test表中的第一行移动到text.txt中

exec?master..xp_cmdshell?'bcp?"SELECT?top?1?*?from?tempdb.dbo.test?"??queryout?e:/test.txt?-c??-Usa?-P123456'

--关闭xp_cmdshell

EXEC?sp_configure?'show?advanced?options',?0;RECONFIGURE;

?

c.BULK?INSERT?

它只能用于数据导入到SQL?SERVER实例中，但是我们一般会选择使用它，因为它比BCP使用工具快。

小例子：

--truncate?table?test

BULK?INSERT?tempdb..test

???FROM?'E:/test.txt'

???WITH?

??????(

?????????FIELDTERMINATOR?=',',--字段分割符号

?????????ROWTERMINATOR?='/n'--换行符号

??????)

select?*?from?test?

/*

id??????????value

-----------?-----------

1????????????s1

2????????????asds

3????????????sadsa

100??????????2asda*/

ps：只写最简单用法,具体参数很多，参考MSDN

?

d.分布式查询

--包含访问OLE?DB?数据源中的远程数据所需的全部连接信息。

--当访问链接服务器中的表时，这种方法是一种替代方法，并且是一种使用OLE?DB?连接并访问远程数据的一次性的临时方法。

--对于较频繁引用OLE?DB?数据源的情况，请改为使用链接服务器。

--A.将OPENROWSET?与SELECT?和SQL?Server?Native?Client?OLE?DB?访问接口一起使用（MSDN）

以下示例使用?SQL?Server?Native?Client?OLE?DB?访问接口访问?TEST.A?表，该表位于远程服务器?SERVER1?上的?POOFLY?数据库中.

SELECT?a.*

FROM?OPENROWSET('SQLNCLI',?'Server=SERVER1;Trusted_Connection=yes;',

?????'SELECT?GroupName,?Name,?DepartmentID

??????FROM?POOFLY.TEST.A

??????ORDER?BY?GroupName,?Name')?AS?a;

--B.?使用Microsoft?OLE?DB?Provider?for?Jet（MSDN）

以下示例通过?Microsoft?OLE?DB?Provider?for?Jet?访问?Microsoft?Access?Northwind?数据库中的?Customers?表。

SELECT?CustomerID,?CompanyName

???FROM?OPENROWSET('Microsoft.Jet.OLEDB.4.0',

??????'C:/Program?Files/Microsoft?Office/OFFICE11/SAMPLES/Northwind.mdb';

??????'admin';'',Customers)

GO

--c.使用OPENROWSET?将文件数据大容量插入varchar(max)?列中

/*

为了导入大型对象数据，OPENROWSET?BULK?子句支持三个选项，允许用户以单行或单列行集导入数据文件的内容。

你可以指定其中一个大型对象选项，而不是使用格式化文件。

大型对象选项包括：

SINGLE_BLOB?

以单行读取data_file?的内容，以varbinary(max)?类型的单列行集返回内容。

SINGLE_CLOB?

以字符读取指定数据文件的内容，以varchar(max)?类型的单行、单列行集返回内容，使用的是当前数据库的排序规则，例如文本或Microsoft?Word?文档。

SINGLE_NCLOB?

以Unicode?读取指定数据文件的内容，以nvarchar(max)?类型的单行、单列行集返回内容，并使用当前数据库的排序规则。

*/

以下示例创建一个用于演示的小型表，并将名为?Text1.txt?的文件中的文件数据插入?varchar(max)?列中。

CREATE?TABLE?my_Test(Document?varchar(max))

GO

INSERT?INTO??my_Test?

?select?*?FROM?OPENROWSET(BULK?N'E:/test.txt',?SINGLE_CLOB)?AS?Document

GO

select?*?from?my_Test

/*

Document

-------------------------------------------------------???

ASDSADASDSADSADSAFKJHFAS?HKLASJHASHBKDSAHKJDHSAKJDHSAKDHSAKDHSA

*/

?

e.SELECT?INTO

关于这个的用法?相信大家都很清楚了?我就不说明了。

?

?

5。优化导入导出数据的一些方法

1。使用最小日志记录:

a.恢复模式是简单模式或者大容量日志记录模式。如果你是完整模式，可以在进行操作前改成大容量日志模式，插入后改回来

b.目的表没有触发器,没有索引,指定了TABLOCK

?

2。将数据从多个客户端并行导入到单个表:

a.如果是完整恢复模式，改成大容量日志模式

b.指定了TABLOCK

c.表上没有索引

?

3。使用批处理:通过设置BCP或者BULK?INSERT的相关选项，是用于可以指定在操作过程中发给SQL的每个批处理的行数。

?

4。禁用触发器和约束:默认情况下是禁用的。如果要检查，可以在复制完成后进行一次更新操作(当然值不可以变)?

?

5。对数据文件中的数据排序:通过设置ORDER提示,提高性能。默认数据文件是不排序的。

?

6。控制锁定行为:指定大容量操作过程获得一个大容量更新表级锁，这样可以减少表上锁的争夺。

?

7。回避DEFAULT:通过设置相关选项,回避在复制数据到表中时，对有DEFAULT的列插入默认值，而是改成在列中值为NULL。