18.2 使用格式化文件跳过表列

Q:

假设d:\some.txt中有如下内容:

line1 line2 line3

现在想用bulk insert将之批量导入一个表中:

create table foo(id int identity,line varchar(8000)) bulk insert foo from 'd:\some.txt' select * from foo

原始需求是给每一行增加一个id字段，但上述bulk insert命令失败了。下面这样可以成功:

create table foo(line varchar(8000)) bulk insert foo from 'd:\some.txt' select * from foo

但这不符合原始需求。

A: scz@nsfocus

以MS SQL Server 2005为例。假设已经创建了foo表:

use tempdb create table foo(id int identity,line varchar(8000))

假设当前OS用户是Administrator，可以使用集成安全性连接到数据库。执行如下命令:

bcp.exe tempdb.dbo.foo format nul -n -T -x -f foo.xml

生成foo.xml如下:

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

在foo.xml基础上修改出一个与some.txt相匹配的some.xml:

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

insert into foo( line ) select line from openrowset( bulk 'd:\some.txt', formatfile='d:\some.xml' ) as temp select * from foo

这会自动设置id字段的值，从1开始。如果"delete from foo"之后再次 "insert into foo"，id不再从1开始，而是在原最大值基础上继续递增。若希望id从 1开始，目前我能想到的办法就是"drop table foo"之后重来。

A: scz@nsfocus

前面用的是XML格式的格式化文件，另有一种TXT格式的格式化文件:

bcp.exe tempdb.dbo.foo format nul -n -T -f foo.fmt

9.0 2 1 SQLINT 0 4 "" 1 id "" 2 SQLCHAR 2 8000 "" 2 line Chinese_PRC_CI_AS

在foo.fmt基础上修改出一个与foo表、some.txt相匹配的some.fmt:

9.0 1 1 SQLCHAR 0 8000 "\r\n" 2 line SQL_Latin1_General_CP1_CI_AS

^^^^^^^                     ^
不能是SQLVARYCHAR           该值与foo表结构对应，从1计

bulk insert foo from 'd:\some.txt' with ( formatfile='d:\some.fmt' ) select * from foo

A: zhouzhen@nsfocus 2011-04-11 14:30

可以不用格式化文件，有更省事的办法:

drop table foo create table foo(line nvarchar(4000)) bulk insert foo from 'c:\boot.ini' alter table foo add id int identity(1,1) select * from foo