?

?

?

?

?

2. 实践

?

2.1 导出数据

?

介绍完BCP的导出导入，以及BULK INSERT的导入，下面进行一些实际的操作。为了接近实际环境，创建一张10个字段的表，包含有几种常用的数据类型，构造2000万的数据，包含中文和英文。为了更快插入测试数据，先不创建索引。在执行下面代码之前，请留意下数据库的日志恢复模式是否设置为大容量模式或简单模式，以及磁盘空间是否足够（我的实践中，数据生成后数据文件和日志文件大概需要40G的空间）。

?

USE AdventureWorks2008R2
GO

IF OBJECT_ID(N'T1') IS NOT NULL
BEGIN
    DROP TABLE T1
END
GO

CREATE TABLE T1 (
    id_ INT,
    col_1 NVARCHAR(50),
    col_2 NVARCHAR(40),
    col_3 NVARCHAR(40),
    col_4 NVARCHAR(40),
    col_5 INT,
    col_6 FLOAT,
    col_7 DECIMAL(18,8),
    col_8 BIT,
    input_date DATETIME DEFAULT(GETDATE())
)
GO

WITH CTE1 AS ( 
SELECT a.[object_id] FROM master.sys.all_objects AS a,master.sys.all_objects AS b,sys.databases AS c
WHERE c.database_id <= 5
)

,CTE2 AS (
SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) as row_no FROM CTE1
)

INSERT INTO T1 (id_,col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8)
SELECT row_no,REPLICATE(N'博客园 ',10),NEWID(),NEWID(),NEWID(),CAST(row_no * RAND() * 10 AS INT),row_no * RAND(),row_no * RAND(),CAST(row_no * RAND() AS INT) % 2
FROM CTE2 WHERE row_no <= 20000000
GO

?

?

code-6

?

?

?

过程要花上几分钟的时间才能完成，请耐心等待一下。

?

使用上面介绍的用法导出数据：

?

EXEC [master]..xp_cmdshell

'BCP AdventureWorks2008R2.dbo.T1 out E:\T1_04.txt -w -T -S KEN\SQLSERVER08R2'

GO

code-7

?

?

?

这里使用-w参数。BCP可以在CMD下导出数据，测试导出2000万条记录，我的笔记本使用了近8分钟左右的时间。BCP同时也可以在SSMS中执行，使用了6分多钟时间，比CMD下速度要快些，生成的文件大小一致，每个文件近5GB。

?

?

figure-9

?

?

figure-10

?

?

?

而对于复杂的大容量导入情况，通常都会需要格式化文件。在以下情况下，必须使用格式化文件：

?

具有不同架构的多个表使用同一数据文件作为数据源。

?

数据文件中的字段数不同于目标表中的列数；例如：

?

目标表中至少包含一个定义了默认值或允许为 NULL 的列。

?

用户不具有对目标表的一个或多个列的 SELECT/INSERT 权限。

?

具有不同架构的两个或多个表使用同一个数据文件。

?

?

?

数据文件和表的列顺序不同。

?

数据文件列的终止字符或前缀长度不同。

?

?

?

这里不使用格式化文件进行导出导入的演示了。详细介绍与使用，请参考联机丛书。

?

?

?

2.2 导入数据

?

使用BULK INSERT把数据导入到目标表数据。为提高性能，可临时删除索引，导完之后再重建索引等。请注意要预留足够的磁盘空间。这里大概花了15分钟导完。

?

?

figure-11

?

?

?

?

?

3. 扩展

?

3.1 数据导出导入自动化与数据接口

?

由于工作关系，有时要开发一些客户的数据接口，每天自动导入比较大量的数据。限制于应用程序等因素影响，所以考虑直接使用SQL SERVER的BULK INSERT每天自动去读取相关目录的中间文件。尽管目录是动态的，但由于中间文件是固定格式的，通过编写动态SQL，最后封闭成存储过程，放到JOB中，配置运行的计划，即可完成自动化的工作。下面简单演示下过程：

?

?

?

3.1.1 编写导入脚本

?

CREATE PROCEDURE sp_import_data
AS
BEGIN 
DECLARE @path NVARCHAR(500)
DECLARE @sql NVARCHAR(MAX)
/*S_PARAMETERS表是可以在应用程序上配置路径的*/
SELECT  @path = value_ + CONVERT(NVARCHAR, getdate(), 23) + '.txt' FROM S_PARAMETERS WHERE [type] = 'Import'
/*T4是一张临时的中间表。先把数据从文件中读入到中间表，最后通过脚本把T4中间表的数据插入到实际的业务表中*/
SET @sql=N'BULK INSERT T4 FROM '''+ @path + '''
WITH (
    FIELDTERMINATOR = ''*'',
    ROWTERMINATOR = ''\n''
    
)'
EXEC (@sql)
END
GO

?

code-8

?

?

?

3.1.2 配置JOB

?

首先要配置好的是SQL SERVER有权限读取相关目录和文件的权限。在Windows服务里，打开SQL SERVER的属性，在Log On页签，使用有足够权限启动SQL SERVER和有权限读取相关目录的用户，比如读取网络盘。

?

?

fi