以下例子在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数字失效。