/*----------------------------------------------------------------
-- Author :feixianxxx(poofly)
-- Date :2010-03-29 14:04:14
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 6.1 (Build 7600: )
----------------------------------------------------------------*/
1。关于大容量数据导入导出的一些方法
SQLSERVER提供多种工具用于各种数据源的数据导入导出,这些数据源包括本文文件、ODBC数据源、OLEDB数据源、ASCII文本文件和EXCEL电子表格。
2.常用工具
DTS:数据转换服务导入导出向导或者DTS设计器创建DTS包
使用SQLSERVER复制发布数据
BCP命令提示实用工具实现SQLSERVER实例和数据文件之间的数据导入导出
BULKINSERT实现从数据文件导入数据到SQLSERVER实例
分布式查询实现从一个数据源选择数据插入到SQLSERVER实例
SELECTINTO语句插入数据表
3.导入导出的数据
1。导入数据的目标表必须存在。导出数据的目标文件如果存在,则将重写上面的内容。如果不存在,则BCP自动创建文件
2。数据文件中的数据必须是字符格式或是先前由bcp工具生成的格式(本机格式)
3。必须对相应的表拥有足够的权限
4。数据导入导出工具的简单用法
4.数据导入导出工具的简单用法
a.DTS
DTS是一组图形工具和可编程对象,是开发者可以将取自完全的不同源的数据析取、转换并合并成一个或者多个。
它的特点就是可以融合完全不同源的数据源这在企业改进中应用很大。
这里涉及到一个DTS包,它是一个有组织的链接、DTS任务、DTS转换和工作流约束的集合。
关于DTS的操作请参看相关具体文献。
b.BCP
它常用于将大量的数据从另外的程序转移到SQLSERVER表中。当然也可以用于将表中数据传输到数据文件中。
下面是一些BCP的简单用法(关于很多的选项使用看相关文档)
--前序,开启xp_cmdshell
--关于xp_cmdshell的一些知识请看http://blog.csdn.net/feixianxxx/archive/2009/08/14/4445603.aspx
EXECsp_configure'show advanced options',1;RECONFIGURE;
EXECsp_configure'xp_cmdshell',1;RECONFIGURE;
--环境
createtabletest
(
idint,
valuevarchar(100)
)
go
inserttestvalues(1,'s1')
inserttestvalues(2,'s2')
inserttestvalues(3,'s3')
inserttestvalues(4,'s4')
go
--1将表的数据导出到TEXT.txt文件中
execmaster..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*intotest1fromtestwhere1=2
execmaster..xp_cmdshell'bcp tempdb.dbo.test1 in e:/test.txt -c -Usa -P123456'
select*fromtest1
--3将TEST表的ID字段复制到TEXT.txt中
execmaster..xp_cmdshell'bcp "SELECT id FROM tempdb.dbo.test" queryout e:/test.dat -T -c'
--4将test表中的第一行移动到text.txt中
execmaster..xp_cmdshell'bcp "SELECT top 1 * from tempdb.dbo.test " queryout e:/test.txt -c -Usa -P123456'
--关闭xp_cmdshell
EXECsp_configure'show advanced options',1;RECONFIGURE;
EXECsp_configure'xp_cmdshell',0;RECONFIGURE;
c.BULKINSERT
它只能用于数据导入到SQLSERVER实例中,但是我们一般会选择使用它,因为它比BCP使用工具快。
小例子:
--truncate table test
BULKINSERTtempdb..test
FROM'E:/test.txt'
WITH
(
FIELDTERMINATOR=',',--字段分割符号
ROWTERMINATOR='/n'--换行符号
)
select*fromtest
/*
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)
以下示例使用SQLServerNativeClientOLEDB访问接口访问TEST.A表,该表位于远程服务器SERVER1上的POOFLY数据库中.
SELECTa.*
FROMOPENROWSET('SQLNCLI','Server=SERVER1;Trusted_Connection=yes;',
'SELECT GroupName, Name, DepartmentID
FROM POOFLY.TEST.A
ORDER BY GroupName, Name')ASa;
--B. 使用Microsoft OLE DB Provider for Jet(MSDN)
以下示例通过MicrosoftOLEDBProviderforJet访问MicrosoftAccessNorthwind数据库中的Customers表。
SELECTCustomerID,CompanyName
FROMOPENROWSET('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)列中。
CREATETABLEmy_Test(Documentvarchar(max))
GO
INSERTINTOmy_Test
select*FROMOPENROWSET(BULKN'E:/test.txt',SINGLE_CLOB)ASDocument
GO
select*frommy_Test
/*
Document
-------------------------------------------------------
ASDSADASDSADSADSAFKJHFAS HKLASJHASHBKDSAHKJDHSAKJDHSAKDHSAKDHSA
*/
e.SELECTINTO
关于这个的用法相信大家都很清楚了我就不说明了。
5。优化导入导出数据的一些方法
1。使用最小日志记录:
a.恢复模式是简单模式或者大容量日志记录模式。如果你是完整模式,可以在进行操作前改成大容量日志模式,插入后改回来
b.目的表没有触发器,没有索引,指定了TABLOCK
2。将数据从多个客户端并行导入到单个表:
a.如果是完整恢复模式,改成大容量日志模式
b.指定了TABLOCK
c.表上没有索引
3。使用批处理:通过设置BCP或者BULKINSERT的相关选项,是用于可以指定在操作过程中发给SQL的每个批处理的行数。
4。禁用触发器和约束:默认情况下是禁用的。如果要检查,可以在复制完成后进行一次更新操作(当然值不可以变)
5。对数据文件中的数据排序:通过设置ORDER提示,提高性能。默认数据文件是不排序的。
6。控制锁定行为:指定大容量操作过程获得一个大容量更新表级锁,这样可以减少表上锁的争夺。
7。回避DEFAULT:通过设置相关选项,回避在复制数据到表中时,对有DEFAULT的列插入默认值,而是改成在列中值为NULL。