sql server Bulk Insert命令详细

BULK INSERT以用户指定的格式复制一个数据文件至数据库表或视图中。
语法:

BULK INSERT [ [ 'database_name'.][ 'owner' ].]{ 'table_name' FROM 'data_file' }
WITH (
  [ BATCHSIZE [ = batch_size ] ],
  [ CHECK_CONSTRAINTS ],
  [ CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] ],
  [ DATAFILETYPE [ = 'char' | 'native'| 'widechar' | 'widenative' ] ],
  [ FIELDTERMINATOR [ = 'field_terminator' ] ],
  [ FIRSTROW [ = first_row ] ],
  [ FIRE_TRIGGERS ],
  [ FORMATFILE = 'format_file_path' ],
  [ KEEPIDENTITY ],
  [ KEEPNULLS ],
  [ KILOBYTES_PER_BATCH [ = kilobytes_per_batch ] ],
  [ LASTROW [ = last_row ] ],
  [ MAXERRORS [ = max_errors ] ],
  [ ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ],
  [ ROWS_PER_BATCH [ = rows_per_batch ] ],
  [ ROWTERMINATOR [ = 'row_terminator' ] ],
  [ TABLOCK ],
)

参数:
'database_name'
是包含指定表或视图的数据库的名称。如果未指定,则系统默认为当前数据库。

'owner'
是表或视图所有者的名称。当执行大容量复制操作的用户拥有指定的表或视图时,owner 是可选项。如果没有指定 owner 并且执行大容量复制操作的用户不拥有指定的表或视图,则 Microsoft® SQL Server? 将返回错误信息并取消大容量复制操作。

'table_name'
是大容量复制数据于其中的表或视图的名称。只能使用那些所有的列引用相同基表所在的视图。有关向视图中复制数据的限制的更多信息,请参见 INSERT。

'data_file'
是数据文件的完整路径,该数据文件包含要复制到指定表或视图的数据。BULK INSERT 从磁盘复制数据(包括网络、软盘、硬盘等)。 data_file 必须从运行 SQL Server 的服务器指定有效路径。如果 data_file 是远程文件,则请指定通用命名规则 (UNC) 名称。

BATCHSIZE [ = batch_size ]
指定批处理中的行数。每个批处理作为一个事务复制至服务器。SQL Server提交或回滚(在失败时)每个批处理的事务。默认情况下,指定数据文件中的所有数据是一个批处理。

CHECK_CONSTRAINTS
指定在大容量复制操作中检查 table_name 的任何约束。默认情况下,将会忽略约束。

CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ]
指定该数据文件中数据的代码页。仅当数据含有字符值大于 127 或小于 32 的 char、varchar 或 text 列时,CODEPAGE 才是适用的。CODEPAGE 值 描述 ACP char、varchar 或 text 数据类型的列从 ANSI/Microsoft Windows® 代码页 ISO 1252 转换为 SQL Server 代码页。 OEM(默认值) char、varchar 或 text 数据类型的列被从系统 OEM 代码页转换为 SQL Server 代码页。 RAW 并不进行从一个代码页到另一个代码页的转换;这是最快的选项。 code_page 特定的代码页号码,例如 850。

DATAFILETYPE [ = {'char' | 'native' | 'widechar' | 'widenative' } ]
指定 BULK INSERT 使用指定的默认值执行复制操作。DATAFILETYPE 值 描述 char(默认值) 从含有字符数据的数据文件执行大容量复制操作。 native 使用 native(数据库)数据类型执行大容量复制操作。要装载的数据文件由大容量复制数据创建,该复制是用 bcp 实用工具从 SQL Server 进行的。 widechar 从含有 Unicode 字符的数据文件中执行大容量复制操作。 widenative 执行与 native 相同的大容量复制操作,不同之处是 char、varchar 和 text 列在数据文件中存储为 Unicode。要装载的数据文件由大容量复制数据创建,该复制是用 bcp 实用工具从 SQL Server 进行的。该选项是对 widechar 选项的一个更高性能的替代,并且它用于使用数据文件从一个运行 SQL Server 的计算机向另一个计算机传送数据。当传送含有 ANSI 扩展字符的数据时,使用该选项以便利用 native 模式的性能。

FIELDTERMINATOR [ = 'field_terminator' ]
指定用于 char 和 widechar 数据文件的字段终止符。默认的字段终止符是 /t(制表符)。

FIRSTROW [ = first_row ]
指定要复制的第一行的行号。默认值是 1,表示在指定数据文件的第一行。

FIRE_TRIGGERS
指定目的表中定义的任何插入触发器将在大容量复制操作过程中执行。如果没有指定 FIRE_TRIGGERS,将不执行任何插入触发器。

FORMATFILE [ = 'format_file_path' ]
指定一个格式文件的完整路径。格式文件描述了含有存储响应的数据文件,这些存储响应是使用 bcp 实用工具在相同的表或视图中创建的。格式文件应该用于以下情况: 数据文件含有比表或视图更多或更少的列。列使用不同的顺序。列分割符发生变化。数据格式有其它的改变。通常,格式文件通过 bcp 实用工具创建并且根据需要用文本编辑器修改。有关更多信息,请参见 bcp 实用工具。

KEEPIDENTITY
指定标识列的值存在于导入文件中。如果没有指定 KEEPIDENTITY,在导入的数据文件中此列的标识值将被忽略,并且 SQL Server 将根据表创建时指定的种子值和增量值自动赋给一个唯一的值。假如数据文件不含该表或视图中的标识列,使用一个格式文件来指定在导入数据时,表或视图中的标识列应被忽略;SQL Server 自动为此列赋予唯一的值。有关详细信息,请参见 DBCC CHECKIDENT。

KEEPNULLS
指定在大容量复制操作中空列应保留一个空值,而不是对插入的列赋予默认值。

KILOBYTES_PER_BATCH [ = kilobytes_per_batch ]
指定每个批处理中数据的近似千字节数(KB)。默认情况下,KILOBYTES_PER_BATCH 未知。

LASTROW [ = last_row ]
指定要复制的最后一行的行号。默认值是 0,表示指定数据文件中的最后一行。

MAXERRORS [ = max_errors ]
指定在大容量复制操作取消之前可能产生的错误的最大数目。不能被大容量复制操作导入的每一行将被忽略并且被计为一次错误。如果没有指定 max_errors,默认值为 0。

ORDER ( { column [ ASC | DESC ] } [ ,...n ] )
指定数据文件中的数据如何排序。如果装载的数据根据表中的聚集索引进行排序,则可以提高大容量复制操作的性能。如果数据文件基于不同的顺序排序,或表中没有聚集索引,ORDER 子句将被忽略。给出的列名必须是目的表中有效的列。默认情况下,大容量插入操作假设数据文件未排序。n是表示可以指定多列的占位符。

ROWS_PER_BATCH [ = rows_per_batch ]
指定每一批处理数据的行数(即 rows_per_bacth)。当没有指定 BATCHSIZE 时使用,导致整个数据文件作为单个事务发送给服务器。服务器根据 rows_per_batch 优化大容量装载。默认情况下,ROWS_PER_BATCH 未知。

ROWTERMINATOR [ = 'row_terminator' ]
指定对于 char 和 widechar 数据文件要使用的行终止符。默认值是 /n(换行符)。

TABLOCK
指定对于大容量复制操作期间获取一个表级锁。如果表没有索引并且指定了 TABLOCK,则该表可以同时由多个客户端装载。默认情况下,锁定行为是由表选项 table lock on bulk load 决定的。只在大容量复制操作期间控制锁会减少表上的锁争夺,极大地提高性能。注释BULK INSERT 语句能在用户定义事务中执行。对于一个用 BULK INSERT 语句和 BATCHSIZE 子句将数据装载到使用多个批处理的表或视图中的用户定义事务来说,回滚它将回滚所有发送给 SQL Server 的批处理。权限只有 sysadmin 和 bulkadmin 固定服务器角色成员才能执行 BULK INSERT。

示例本例从指定的数据文件中导入订单详细信息,该文件使用竖杠 (|) 字符作为字段终止符,使用 |/n 作为行终止符。

BULK INSERT Northwind.dbo.[Order Details] FROM 'f:/orders/lineitem.tbl'
WITH (
  FIELDTERMINATOR = '|',
  ROWTERMINATOR = '|/n'
)

本例指定 FIRE_TRIGGERS 参数。

BULK INSERT Northwind.dbo.[Order Details] FROM 'f:/orders/lineitem.tbl'
WITH (
  FIELDTERMINATOR = '|',
  ROWTERMINATOR = ':/n',
  FIRE_TRIGGERS
)
=============================================================
BULK INSERT
 [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
  FROM 'data_file'
  [ WITH
  (
 [ [ , ] BATCHSIZE = batch_size ] --BATCHSIZE指令来设置在单个事务中可以插入到表中的记录的数量
 [ [ , ] CHECK_CONSTRAINTS ]  --指定在大容量导入操作期间,必须检查所有对目标表或视图的约束。若没有 CHECK_CONSTRAINTS 选项,则所有 CHECK 和 FOREIGN KEY 约束都将被忽略,并且在此操作之后表的约束将标记为不可信。
 [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] --指定该数据文件中数据的代码页
 [ [ , ] DATAFILETYPE =
  { 'char' | 'native'| 'widechar' | 'widenative' } ] --指定 BULK INSERT 使用指定的数据文件类型值执行导入操作。
 [ [ , ] FIELDTERMINATOR = 'field_terminator' ] --标识分隔内容的符号
 [ [ , ] FIRSTROW = first_row ] --指定要加载的第一行的行号。默认值是指定数据文件中的第一行
 [ [ , ] FIRE_TRIGGERS ]  --是否启动触发器
 [ [ , ] FORMATFILE = 'format_file_path' ]
 [ [ , ] KEEPIDENTITY ] --指定导入数据文件中的标识值用于标识列
 [ [ , ] KEEPNULLS ] --指定在大容量导入操作期间空列应保留一个空值,而不插入用于列的任何默认值
 [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
 [ [ , ] LASTROW = last_row ] --指定要加载的最后一行的行号
 [ [ , ] MAXERRORS = max_errors ] --指定允许在数据中出现的最多语法错误数,超过该数量后将取消大容量导入操作。
 [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] --指定数据文件中的数据如何排序
 [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
 [ [ , ] ROWTERMINATOR = 'row_terminator' ] --标识分隔行的符号
 [ [ , ] TABLOCK ]  --指定为大容量导入操作持续时间获取一个表级锁
 [ [ , ] ERRORFILE = 'file_name' ] --指定用于收集格式有误且不能转换为 OLE DB 行集的行的文件。
  )] 

下面写个个简单的应用例子

bulk insert xsxt.dbo.tabletest from 'c:/data.txt'
 with(
 FIELDTERMINATOR=',',
 ROWTERMINATOR='/n'
)
(0)

相关推荐

  • mssql server 存储过程里,bulk insert table from '路径+文件',路径固定,文件名不固定的实现方法

    动态语句,   bulk   insert的from   子句只能使用常量.  CREATE     proc   test      @fn   varchar(50)      as      declare   @path   varchar(100)      set   @path=   'd:\'      exec('      bulk   INSERT   table   FROM   '''+@path+@fn+'''            WITH             

  • 使用BULK INSERT大批量导入数据 SQLSERVER

    开门见山,直接以例子介绍: 复制代码 代码如下: CREATE TABLE [dbo].[course]( [id] [int] NULL, [name] [nvarchar](50) NULL, [CourseType] [nvarchar](50) NULL, [Course] [float] NULL ) 导入数据: 将下面的数据存储为文本文件或SQL文件 2,李刚,语文,89;3,李刚,数学,79;3,李刚,英语,69;4,李刚,化学,89 导入语句: 复制代码 代码如下: BULK I

  • SQL Server Bulk Insert 只需要部分字段时的方法

    根据一般做法的话,导出部分字段时没有办法生成格式化XML文件,所以导入时就没有办法格式化导入数据. 我想到两点,1.手工修改格式化XML文件,2.创造一个能生成格式化XML文件的中间对象. 在MSDN中寻找方法时,突然想到可以使用视图来做中间对象,于是就搭一个测试下.以下是测试记录: 复制代码 代码如下: USE master GO CREATE DATABASE [db_test] GO USE db_test GO CREATE TABLE dbo.T_test( ID [int] IDEN

  • sql server Bulk Insert命令详细

    BULK INSERT以用户指定的格式复制一个数据文件至数据库表或视图中. 语法: BULK INSERT [ [ 'database_name'.][ 'owner' ].]{ 'table_name' FROM 'data_file' } WITH ( [ BATCHSIZE [ = batch_size ] ], [ CHECK_CONSTRAINTS ], [ CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] ], [ DATAFILE

  • 在SQL Server中使用命令调用SSIS包的具体方法

    具体操作步骤如下: 1.首先,当然是要在Business Intelligence中设计好包,并调试通过. 2.然后,有两种方式可以在SQL Server中使用命令运行SSIS包 第一种是直接执行ssis包文件,命令如: exec xp_cmdshell 'dtexec /f "c:\test.dtsx"' 第二种是将包文件发布或者导入到Integration Services服务中,然后再执行. 发布包是在Business Intelligence中生成发布文件,并发布到Integr

  • SQL Server 数据库的备份详细介绍及注意事项

    SQL Server 备份 前言 为什么要备份?理由很简单--为了还原/恢复.当然,如果不备份,还可以通过磁盘恢复来找回丢失的文件,不过SQL Server很生气,后果很严重.到时候你就知道为什么先叫你备份一次再开始看文章了.∩__∩.本系列将介绍SQL Server所有可用的备份还原功能,并尽可能用实例说话. 什么是备份?SQL Server基于Windows,以文件形式存放资料,所以备份就是Windows上SQL Server相关文件的一个某个时间点的副本.根据备份类型的不同,副本的种类和内

  • SQL Server 远程连接服务器详细配置(sp_addlinkedserver)

    远程链接服务器详细配置 --建立连接服务器 EXEC sp_addlinkedserver '远程服务器IP','SQL Server' --标注存储 EXEC sp_addlinkedserver @server = 'server', --链接服务器的本地名称.也允许使用实例名称,例如MYSERVER\SQL1 @srvproduct = 'product_name' --OLE DB数据源的产品名.对于SQL Server实例来说,product_name是'SQL Server' , @

  • SQL Server利用bcp命令把SQL语句结果生成文本文件

    在SQL Server里可以调用DOS下的命令行工具bcp来实现把表里的数据或者SQL语句结果生成文本文件. BCP命令的参数格式: 复制代码 代码如下: BCP {dbtable | query} {in | out | queryout | format} datafile [-m maxerrors] [-f formatfile] [-e errfile] [-F firstrow] [-L lastrow] [-b batchsize] [-n native type] [-c cha

  • SQL Server 2005 数据库复制详细介绍

    对于一个地域分散的大型企业组织来说,构建具有典型的分布式计算机特征的大型企业管理信息系统时,总要解决一个很重要的问题:如何在多个不同数据库服务器之间保证共享数据的一致性.之所以有这个重要的问题在于企业组织在不同地点对具有相同结构的本地数据库进行修改,但要保证修改后的数据库有相同的结果,其本质就是在对本地数据库的修改体现在其他具有相同数据的远程数据库中.那么如何解决这个问题就要用到数据库的复制技术. SQL SERVER提供了内置的复制能力,复制组件并不是附加产品,而是核心引擎的一部分.在复制这个

  • SQL Server 2005恢复数据库详细图文教程

    不少需要用到sql2005的程序,有很多新手还是会操作,这里写个详细的图文教程送个菜鸟们,高手请飘过.适用于独立主机的朋友使用,如果你还没安装,请按照这个教程来安装 SQL Server 2005图文安装教程,超详细 下面是SQL Server 2005恢复数据库的详细过程 1:打开SQL Server Management Studio并登录,这个一般在开始--程序里面找到 2:鼠标右键单击数据库--新建数据库 3:弹出来的框里,填写数据库名称,我这里填写的是sqlqtdy,这个根据自己需求来

  • SQL Server 2005基础知识详细整理

    1. ACID:指数据库事务正确执行的四个基本要素缩写:1.原子性2.一致性3.隔离性4.持久性 2.数据库对象:表(table) 视图(view) 存储过程(stored procedure) 函数(function)索引(index) 3.SQL Server 2005中包含master.model.msdb.tempdb四个系统数据库. 4.使用T-SQL语句创建数据库:CREATE DATABASE [ApressFinacial] ON  PRIMARY ( NAME = N'Apre

  • SQL Server常用管理命令小结

    1. 查看数据库的版本 select @@version 2. 查看数据库所在机器操作系统参数 exec master..xp_msver 3. 查看数据库启动的参数 sp_configure 4. 查看数据库启动时间 select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1 查看数据库服务器名和实例名 print 'Server Name...............:' + conver

随机推荐