bcp格式化导入导出_流烟

http://blog.sina.com.cn/u/1559194947

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

bcp格式化导入导出

(2011-03-30 21:55:58)

标签：

bcp

格式化

导入导出

分类：数据库

一、建立测试表

CREATE TABLE t_user (
ID int identity PRIMARY KEY,
UserID varchar(50) not null,

Pwd varchar(50) not null,
UserName varchar(50) null,
birth datetime null,
phone varchar(50) null,
loginTime datetime not null default getdate()
)

CREATE TABLE t_user2 (
ID int identity PRIMARY KEY,
UserID varchar(50) not null,
Pwd varchar(50) not null,
UserName varchar(50) null,
)

二、生成格式化文件（有3种方法）

1、bcp命令中不使用-c，则根据命令提示生成相应的格式化文件

bcp testData.dbo.t_user out c:\a.txt -T

请输入 ID [int] 字段的文件存储类型: int
请输入字段 ID [0] 的前缀长度: 0
请输入字段终止符 [none]: \t

请输入 UserID [char] 字段的文件存储类型: char
请输入字段 UserID [2] 的前缀长度: 0
请输入字段 UserID [50] 的长度: 50
请输入字段终止符 [none]: \t

请输入 Pwd [char] 字段的文件存储类型: char
请输入字段 Pwd [2] 的前缀长度: 0
请输入字段 Pwd [50] 的长度: 50
请输入字段终止符 [none]: \t

请输入 UserName [char] 字段的文件存储类型: char
请输入字段 UserName [2] 的前缀长度: 0
请输入字段 UserName [50] 的长度: 50
请输入字段终止符 [none]: \t

请输入 birth [datetime-null] 字段的文件存储类型: datetime
请输入字段 birth [1] 的前缀长度: 0
请输入字段终止符 [none]: \t

请输入 phone [char] 字段的文件存储类型: char
请输入字段 phone [2] 的前缀长度: 0
请输入字段 phone [50] 的长度: 50
请输入字段终止符 [none]: \t

请输入 loginTime [datetime] 字段的文件存储类型: datetime
请输入字段 loginTime [0] 的前缀长度: 0
请输入字段终止符 [none]: \r\n

是否要将此格式信息保存在一个文件中? [Y/n] Y
宿主文件名 [bcp.fmt]: c:\t_user.fmt

开始复制...

已复制 4 行。
网络数据包大小(字节): 4096
总时钟时间(毫秒) : 16 平均值: (每秒 187.50 行。)

t_user.fmt格式化文件如下：

9.0
7
1       SQLINT        0       4       "\t"    1     ID                   ""
2       SQLCHAR       0       50      "\t"    2     UserID               Chinese_PRC_CI_AS
3       SQLCHAR       0       50      "\t"    3     Pwd                  Chinese_PRC_CI_AS
4       SQLCHAR       0       50      "\t"     4     UserName             Chinese_PRC_CI_AS
5       SQLDATETIME   0       8       "\t"    5     birth                ""
6       SQLCHAR       0       50      "\t"     6     phone                Chinese_PRC_CI_AS
7       SQLDATETIME   0       8       "\r\n"   7     loginTime            ""

t_user.fmt文件说明

第一行(9.0)确定了目前使用的bcp版本。第二行(6) 确定了表中的列数，接下来的三行为列的相关信息：
* 第一个列为列在源文件中出现的顺序。
* 第二个列显示每个列的源文件数据类型。因为在生成文件时指定了-c，在从数据文件中提取时，所有的列都用字符型数据类型。插入数据时，SQL Server会将数据转换到正确的类型。
* 第三个列表示列的前缀长度，SQL Server通常会用它来提供最紧凑的文件存储。在创建格式文件时如果你指定-c，那么就会自动用到0。
* 第四个列代表该列数据类型字节长度。
* 第五个列表明如何终止行和列。由于在创建格式文件时用了"-t,"，源文件的列值就必须通过逗号终止。
* 第六个列映射这些列在SQL Server表中的排列顺序。
* 第七个和最后一个域提供了SQL Server表中字符列的整理信息。

2、生成格式化文件

bcp testData.dbo.t_user format nul -f C:\t_user.fmt -c -T

t_user.fmt格式化文件如下：

3、生成xml格式化文件

bcp testData.dbo.t_user format nul -f C:\t_user.fmt -c -x -f c:\t_user.xml -T

t_user.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="\t" MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="50" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="50" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="50" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="24"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="50" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="ID" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="UserID" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="Pwd" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="UserName" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="5" NAME="birth" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="6" NAME="phone" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="7" NAME="loginTime" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

t_user.xml文件说明:

    <RECORD>父节点中的内容是对源文件的格式化定义，即你要导入的数据文件
        <FIELD ID="1"(这里是源数据字段ID) xsi:type="CharTerm"(字段类型) TERMINATOR="\t"(字段分隔符) MAX_LENGTH="12"(最大长度)/>
            不难看出，我们最常修改的就是TERMINATOR，可以修改为"|"等你需要的分隔符,注意最后一个字段的分隔符为"\r\n"
    </RECORD>
    <ROW>该父节点的内容是目标数据的格式化定义，即你需要导入数据文件的地方
        <COLUMN SOURCE="1"(这个非常重要，指定的是上面源数据的字段ID，当源数据和目标数据字段顺序或数目不一致时，我们可以修改这里达到我们想要的结果) NAME="ID"(目标字段名称) xsi:type="SQLINT"(目标字段类型)/>
    </ROW>

三、格式化导入

1、使用非XML格式化文件导入

导入数据a.txt文件如下：(制表符分隔)

001 001 1994-01-01 00:00:00.000
002 002
003 003 88832881
004 004 1982-03-25 00:00:00.000 88832888

t_user.fmt格式化文件如下：

9.0
4
1       SQLCHAR       0       50      "\t"     2     UserID                 Chinese_PRC_CI_AS
2       SQLCHAR       0       50      "\t"     3     Pwd                    Chinese_PRC_CI_AS
3       SQLCHAR       0       24      "\t"     5     birth                  ""
4       SQLCHAR       0       50      "\r\n"   6     phone                  Chinese_PRC_CI_AS

t_user2.fmt格式化文件如下：

9.0
4
1       SQLCHAR       0       50      "\t"     2     UserID                 Chinese_PRC_CI_AS
2       SQLCHAR       0       50      "\t"     3     Pwd                    Chinese_PRC_CI_AS
3       SQLCHAR       0       24      "\t"     0     ""                     ""
4       SQLCHAR       0       50      "\r\n"   0     ""                     ""

bcp testData.dbo.t_user in c:\a.txt -f C:\t_user.fmt -T

select * from t_user

ID	UserID	Pwd	UserName	birth	phone	loginTime
1	001	001	NULL	1994-01-01 00:00:00.000	NULL	2011-03-30 21:43:35.790
2	002	002	NULL	NULL	NULL	2011-03-30 21:43:35.790
3	003	003	NULL	NULL	88832881	2011-03-30 21:43:35.790
4	004	004	NULL	1982-03-25 00:00:00.000	88832881	2011-03-30 21:43:35.790

bcp testData.dbo.t_user2 in c:\a.txt -f C:\t_user2.fmt -T

select * from t_user2

ID	UserID	Pwd	UserName
1	001	001	NULL
2	002	002	NULL
3	003	003	NULL
4	004	004	NULL

2、使用XML格式化文件导入

xml格式化选择列导入实现不了。

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：Sql Server2005使用row_number()删除重复记录

后一篇：SQL Server BCP导入导出数据

新浪BLOG意见反馈留言板　欢迎批评指正