Bulk Insert 实例

 以下例子在Windows XP + MS SQL Server 2000 Personal Edition 上运行通过。

--建表
use tempdb
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[book]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[book]
GO

CREATE TABLE [dbo].[book] (
 [book_id] [char] (3)   COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [book_category] [char] (3)  COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [book_date] [varchar] (50)  COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [book_pub] [nvarchar] (50)  COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

delete from book
go

select * from book
go

SET ANSI_WARNINGS OFF
BULK INSERT book
   FROM 'e:/book.dat'
   WITH
 (
 datafiletype='char',
 FIELDTERMINATOR ='',
 FORMATFILE ='e:/book.fmt',
 firstrow=1,
 tablock,
 rowterminator='/r/n');
GO
SELECT * FROM book;
GO

--If there is a following error,
--Msg 8152, Level 16, State 14, Line 3
--String or binary data would be truncated.
--The statement has been terminated.
--then SET ANSI_WARNINGS OFF before you do the bulk insert. This legacy setting turns of the check for truncation.

--book.dat
--the following is the plain text data file:
aa31012006/09/16 12:00:00aaafvdav gfdag gfsg
aa42022006/09/16 12:00:0012345678901234567890123456789012345678901234567890
aa53032006/09/16 12:00:00cccfvd
aa53032006/09/16 12:00:001234567890Mobile 5.0 系统 死屏、找不到CFSD卡和电池大量消耗的解决办法.txt

--以上第四行上的最后一个t字符因为超过50个字符，被数据库自动截断。

--book.fmt
--the following is the format file for the plain text data file(with no delimeter for data fields) to be imported:
8.0
4
1 SQLCHAR  0 3 "" 1 book_id   Chinese_PRC_CS_AS
2 SQLCHAR  0 3 "" 2 book_category  Chinese_PRC_CS_AS
3 SQLCHAR  0 19 "" 3 book_date  Chinese_PRC_CS_AS
4 SQLCHAR  0 50 "/r/n" 4 book_pub  Chinese_PRC_CS_AS

--if there is delimeter for data field in the data file, we do not need the previous format file.
--Just add FIELDTERMINATOR ='delimeter character' and no FORMATFILE option for the BULK INSERT command.
--3,3,19 限制了需要读取的字符（不大于database table中的 field的length），不可超过这个数字从数据行上读取数据。
--若这个数字大于表中相对应的field的长度，则取field的最大长度为可以取得的长度。
--在format文件的第四行上，使用了"/r/n"作为分隔符，所以50这个field length数字失效。