SQLSERVER 大数据量插入命令

 

BULK INSERT是SQLSERVER中提供的一条大数据量导入的命令，它运用DTS(SSIS)导入原理，可以从本地或远程服务器上批量导入数据库或文件数据。批量插入是一个独立的操作，优点是效率非常高。缺点是出现问题后不可以回滚。

　　BULK INSERT是用来将外部文件以一种特定的格式加载到数据库表的T-SQL命令。该命令使开发人员能够直接将数据加载到数据库表中，而不需要使用类似于Integration Services这样的外部程序。虽然BULK INSERT不允许包含任何复杂的逻辑或转换，但能够提供与格式化相关的选项，并告诉我们导入是如何实现的。BULK INSERT有一个使用限制，就是只能将数据导入SQL Server。

插入数据下面的例子能让我们更好的理解如何使用BULK INSERT命令。首先，我们来创建一个名为Sales的表，我们将要把来自文本文件的数据插入到这个表中。

本地数据操作：

 当我们使用BULK INSERT命令来插入数据时，创建的表有无触发器需要不同的配置，我们分别举例来看：

【A没有触发器的表】

　　CREATE TABLE [dbo].[Sales] 

　　( 

　　 [SaleID] [int], 

　　 [Product] [varchar](10) NULL, 

　　 [SaleDate] [datetime] NULL, 

　　 [SalePrice] [money] NULL 

　　) 

    【B有触发器的表】

　　还是在上面表的基础上，我们创建触发器，用来打印插入到表中的记录的数量。

　　CREATE TRIGGER tr_Sales 

　　ON Sales 

　　FOR INSERT 

　　AS 

　　BEGIN 

　　PRINT CAST(@@ROWCOUNT AS VARCHAR(5)) + ' rows Inserted.' 

　　END 

    这里我们选择文本文件作为源数据文件，文本文件中的值通过逗号分割开。该文件包含1000条记录，而且其字段和Sales表的字段直接关联。由于该文本文件中的值是由逗号分割开的，我们只需要指定FIELDTERMINATOR即可。注意，当下面这条语句运行时，我们刚刚创建的触发器并没有启动：

A：没有触发器的操作

BULK INSERT Sales FROM 'C:\Users\Administrator\Desktop\Sales.txt' WITH (FIELDTERMINATOR = ',')

B：有触发器的操作

　　当我们要的数据量非常大时，有时候就需要启动触发器。下面的脚本使用了FIRE_TRIGGERS选项来指明在目标表上的任何触发器都应当启动：

　　BULK INSERT Sales FROM 'C:\Users\Administrator\Desktop\Sales.txt' WITH (FIELDTERMINATOR = ',', FIRE_TRIGGERS)

　　C：举一反三

我们可以使用BATCHSIZE指令来设置在单个事务中可以插入到表中的记录的数量。假如文件中共有1000条记录。在前一个例子中，所有的1000条记录都在同一个事务中被插入到目标表里。下面的例子，我们将BATCHSIZE参数设置为2，也就是说要对该表执行500次独立的插入事务。这也意味着启动500次触发器，所以将有500打印指令输出到屏幕上。

BULK INSERT Sales FROM 'c:\Salestxt' WITH (FIELDTERMINATOR = ',', FIRE_TRIGGERS, BATCHSIZE = 2)

远程数据操作：

　　BULK INSERT不仅仅可以应用于SQL Server 2005的本地映射驱动器。下面的语句将告诉我们如何从名为FileServer的远程服务器的D盘中将SalesText文件的数据导入。

　　BULK INSERT Sales FROM 'FileServerD$:\Sales.txt' WITH (FIELDTERMINATOR = ',')

　　有时候，我们在执行导入操作以前，最好能先查看一下将要输入的数据。下面的语句在使用BULK命令时，使用了OPENROWSET函数，以便从SalesText文本文件中读取源数据。该语句同时还需要使用一个格式文件(此处没有列出文件的具体内容)来表明该文本文件中的数据格式。

　　SELECT * 

　　FROM OPENROWSET(BULK 'c:SalesText.txt' , 

　　FORMATFILE='C:\SalesFormat.Xml' 

　　) AS mytable; 

GO