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

https://scz.617.cn/misc/201104081240.txt

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如下:

--------------------------------------------------------------------------
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <RECORD>
        <FIELD ID="1" xsi:type="NativeFixed" LENGTH="4"/>
        <FIELD ID="2" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="8000" COLLATION="Chinese_PRC_CI_AS"/>
    </RECORD>
    <ROW>
        <COLUMN SOURCE="1" NAME="id" xsi:type="SQLINT"/>
        <COLUMN SOURCE="2" NAME="line" xsi:type="SQLVARYCHAR"/>
    </ROW>
</BCPFORMAT>
--------------------------------------------------------------------------

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

--------------------------------------------------------------------------
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <RECORD>
        <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    </RECORD>
    <ROW>
        <COLUMN SOURCE="1" NAME="line" xsi:type="SQLVARYCHAR"/>
    </ROW>
</BCPFORMAT>
--------------------------------------------------------------------------

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