关于MSSQL数据大容量的导入导出小结
关于MSSQL数据大容量的导入导出小结
/*----------------------------------------------------------------
-- 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。关于大容量数据导入导出的一些方法
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。数据导入导出工具的简单用法
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 数据库中.
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。
原文地址: http://blog.csdn.net/feixianxxx/article/details/5427485
