用法: bcp {dbtable
|
query} {
in
|
out
|
queryout
|
format} 数据文件
[
-m 最大错误数
]
[
-f 格式化文件
]
[
-e 错误文件
]
[
-F 首行
]
[
-L 末行
]
[
-b 批大小
]
[
-n 本机类型
]
[
-c 字符类型
]
[
-w 宽字符类型
]
[
-N 将非文本保持为本机类型
]
[
-V 文件格式版本
]
[
-q 带引号的标识符
]
[
-C 代码页说明符
]
[
-t 字段终止符
]
[
-r 行终止符
]
[
-i 输入文件
]
[
-o 输出文件
]
[
-a 数据包大小
]
[
-S 服务器名称
]
[
-U 用户名
]
[
-P 密码
]
[
-T 可信连接
]
[
-v 版本
]
[
-R 允许使用区域设置
]
[
-k 保留空值
]
[
-E 保留标识值
]
[
-h"加载提示"
]
[
-x 生成xml 格式化文件
]
--
不利用格式化文件的导入导出(以下示例皆省略了-S参数，因为是在本机，又是默认实例)
--
简单导出表
exec
master..xp_cmdshell
'
bcp db_oa.dbo.T_User_list out c:\a.xls -c -T
'
--
"-T"信任连接
exec
master..xp_cmdshell
'
bcp db_oa.dbo.T_User_list out c:\a.txt -c -Usa -Pxxxxx
'
--
过滤导出表
exec
master..xp_cmdshell
'
bcp "select top 3 * from db_oa.dbo.t_user_list" queryout c:\a.txt -c -T
'
--
简单导入表
exec
master..xp_cmdshell
'
bcp db_oa.dbo.T_User_list_2 in c:\a.txt -c -T
'
exec
master..xp_cmdshell
'
bcp db_oa.dbo.T_User_list_2 in c:\a.txt -c -T -E
'
--
加上"-E"启用identity_insert on
--
下面是利用XML格式化文件进行数据的导入！
--
创建测试表
USE
db_oa;
GO
CREATE
TABLE
myTestFormatFiles (
Col1
smallint
,
Col2
nvarchar
(
50
),
Col3
nvarchar
(
50
),
Col4
nvarchar
(
50
)
);
GO
--
输出XML格式化文件
--
说明一下：-t","是指定字段分隔符，稍后我们会讲到
exec
master..xp_cmdshell
'
bcp db_oa..MyTestFormatFiles format nul -c -t"," -x -f c:\myTestFormatFiles.Xml -T
'
/*
格式化后的文件内容如下：
<?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="," MAX_LENGTH="7"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="Chinese_PRC_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
<COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>
这里我们对文件关键字稍作解释：
<RECORD>父节点中的内容是对源文件的格式化定义，即你要导入的数据文件
<FIELD ID="1"(这里是源数据字段ID) xsi:type="CharTerm"(字段类型) TERMINATOR=","(字段分隔符) MAX_LENGTH="7"(最大长度)/>
不难看出，我们最常修改的就是TERMINATOR，可以修改为"|"等你需要的分隔符,注意最后一个字段的分隔符为"\r\n"
</RECORD>
<ROW>该父节点的内容是目标数据的格式化定义，即你需要导入数据文件的地方
<COLUMN SOURCE="1"(这个非常重要，指定的是上面源数据的字段ID，当源数据和目标数据字段顺序或数目不一致时，我们可以修改这里达到我们想要的结果) NAME="Col1"(目标字段名称) xsi:type="SQLSMALLINT"(目标字段类型)/>
</ROW>
*/
--
下面我们创建源数据文件，将以下内容粘贴到我们指定的数据文件C:\myTestFormatFiles-c.txt中
/*
10,Field2,Field3,Field4
15,Field2,Field3,Field4
46,Field2,Field3,Field4
58,Field2,Field3,Field4
*/
--
利用bcp格式化文件导入数据
exec
master..xp_cmdshell
'
bcp db_oa..myTestFormatFiles in C:\myTestFormatFiles-c.txt -f C:\myTestFormatFiles.Xml -T
'
--
查看一下导入的数据
select
*
from
myTestFormatFiles
/*
Col1 Col2 Col3 Col4
10 Field2 Field3 Field4
15 Field2 Field3 Field4
46 Field2 Field3 Field4
58 Field2 Field3 Field4
*/
--
使用BULK INSERT导入数据
USE
db_oa;
GO
DELETE
myTestFormatFiles;
GO
BULK
INSERT
myTestFormatFiles
FROM
'
C:\myTestFormatFiles-c.txt
'
WITH
(FORMATFILE
=
'
C:\myTestFormatFiles.Xml
'
);
GO
SELECT
*
FROM
myTestFormatFiles;
GO
/*
Col1 Col2 Col3 Col4
10 Field2 Field3 Field4
15 Field2 Field3 Field4
46 Field2 Field3 Field4
58 Field2 Field3 Field4
*/
--
使用OPENROWSET 大容量行集提供程序导入
USE
db_oa;
DELETE
myTestFormatFiles;
GO
INSERT
INTO
myTestFormatFiles
SELECT
*
FROM
OPENROWSET
(
BULK
'
C:\myTestFormatFiles-c.txt
'
,
FORMATFILE
=
'
C:\myTestFormatFiles.Xml
'
)
as
t1 ;
GO
SELECT
*
FROM
myTestFormatFiles;
GO
/*
Col1 Col2 Col3 Col4
10 Field2 Field3 Field4
15 Field2 Field3 Field4
46 Field2 Field3 Field4
58 Field2 Field3 Field4
*/
--
删除测试表
DROP
TABLE
myTestFormatFiles
