bcp、BULK INSERT 和 OPENROWSET(BULK)

bcp（大容量复制程序）实用工具

BULK INSERT 以用户指定的格式将数据文件加载至数据库表或视图中。

OPENROWSET 大容量行集提供程序

 

 

 

go

---------批量写数据---将XML数据批量写入数据库

if exists (select * from sys.objects where object_id=object_id(N'Users'))

drop table Users

create table Users

(

imgName VARCHAR(100),

name VARCHAR(100),

intro VARCHAR(100)

)

select * from Users

go

DECLARE @HDOC INT    --文档句柄

DECLARE @XML XML

SELECT @XML = BULKCOLUMN FROM OPENROWSET(BULK 'E:/keji.xml',SINGLE_BLOB) AS TEMP

--SELECT 语句中的 FROM 子句可以调用 OPENROWSET 大容量行集提供程序 [OPENROWSET(BULK...)] 来代替表名

--调用 OPENROWSET(BULK...) 可以直接在文件中查询数据，而无需将数据导入到表中

--SINGLE_BLOB 将 data_file 的内容作为类型为 varbinary(max) 的单行单列行集返回。

--使用系统存储过程SP_XML_PREPAREDOCUMENT分析XML字符串

EXEC SP_XML_PREPAREDOCUMENT @HDOC OUTPUT, @XML

--使用OPENXML从SQL Server 的内部缓存查询数据

insert into Users  --插入新建表

SELECT * FROM OPENXML(@HDOC,N'/ItemList/Item',2)

WITH 

( 

imgName VARCHAR(100),

name VARCHAR(100),

intro VARCHAR(100)

)

--使用系统存储过程SP_XML_REMOVEDOCUMENT释放内存

EXEC SP_XML_REMOVEDOCUMENT @HDOC

 

 

go

.XML文件格式

<?xml version="1.0" standalone="yes"?>

<ItemList>

  <Item>

    <imgName>tech_11</imgName>

    <name>生命术</name>

    <intro>生命术，提高人型战斗单位的生命上限，每级可增加2%。</intro>

  </Item>

  <Item>

    <imgName>tech_12</imgName>

    <name>狂暴术</name>

    <intro>狂暴术，提高人型战斗单位的攻击力，每级可增加2%。</intro>

  </Item>

</ItemList>

go

EXEC xp_cmdshell 'dir *.exe,NO_OUTPUT';

EXEC master..xp_cmdshell 'dir *.exe'

go

DECLARE @result int

EXEC @result = xp_cmdshell 'dir *.exe'

IF (@result = 0)

   PRINT 'Success'

ELSE

   PRINT 'Failure'

go

select * from book

-- OPENROWSET 用于 SELECT 和 SQL 本机客户端 OLE DB 访问接口

select a.*,address.*

from openrowset('SQLNCLI','Server=.;Trusted_Connection=yes;',

'select book_Id,book_Author,book_Name from study.dbo.book')

as a inner join address on a.book_Id=address.address_id

go

-- 使用 OPENROWSET 将文件数据大容量插入

if exists (select * from sys.objects where object_id=object_id(N'Users'))

drop table Users

create table Users

(

FileName nvarchar(60),

FileTyle nvarchar(60),

Document varbinary(max)

)

select * from Users

   --将名为 Text1.txt 的文件（位于 E: 根目录）中的文件数据插入 varbinary(max) 列中

insert into Users

select 'Text1.txt' as FileName,

'.txt' as FileTyle ,*

from openrowset(bulk N'E:/Text1.txt',single_blob) as Document

 

---------------------------------------------------------------------------

/*************--- BULK INSERT 用法小实例------
---表A中有5个字段， 
---分别为金币排名（goldtop）、用户名（username）、时间(time)、金币数(gold)、地区（address） 
---要求： 
---先清空表A的里的所有数据。 
---然后，插入本地路径下的一个TXT文本文件的内容。 
---如路径为：E:/1.txt 
---路径通过存储过程参数的形式传递进去。 
---TXT的文本内容为格式为：148|小明|2009-06-03|10|重庆 
---TXT的格式和表A的字段相对应，但有“|”符号分割 
---请各位帮忙把想对应的数据存入到相应的字段。
---********************/
go
if exists (select * from sys.objects where object_id=object_id(N'tb'))
drop table tb
create table 
tb
(
	goldtop int,
	username varchar(10),
	[time] datetime,
	gold int,
	address varchar(10)
)
select * from tb

create proc test_tb
@path varchar(100)
as
truncate table tb --删除表中的所有行，而不记录单个行删除操作。
exec('BULK INSERT tb
FROM '''+@path+'''
WITH
(
   FIELDTERMINATOR = ''|'',
   ROWTERMINATOR = ''/n''
)
')
go
exec test_tb 'E:/1.txt'

--FIELDTERMINATOR = 'field_terminator'
--指定用于 char 和 widechar 数据文件的字段终止符。默认的字段终止符是 /t（制表符）。
--ROWTERMINATOR = 'row_terminator'
--指定对于 char 和 widechar 数据文件要使用的行终止符。默认行终止符为 /n（换行符）。

具体参数请参考MSDN

以上乃个人小练