【SQL SERVER中关于大容量数据的导入导出总结】

博文地址 请进：http://blog.csdn.net/feixianxxx/archive/2010/03/29/5427485.aspx

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的简单用法(关于很多的选项使用看相关文档)

SQL code

   
   

    
    
    
    --
    
    前序,开启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使用工具快。
小例子：

SQL code

   
   

    
    
    
    --
    
    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.分布式查询

SQL code

   
   

    
    
    
    --
    
    包含访问 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。