学步园

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

 

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', 1;RECONFIGURE;

EXEC sp_configure 'xp_cmdshell', 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