使用bcp批量导入数据时碰到的一些问题及解决

因为是初次使用bcp来初次导入数据，有些地方难免有些摸不着门道，碰了几次鼻子后，大约弄懂了一点，但是还是比较片面见识，甚至可能还有些错误之处，斗胆拿出来晒晒。

 

Code
先期准备：生成一个新表
create table testtb
(
productcode varchar(10),
merchantprice numeric(10,2),
totalstocks int,
productid int
)
go

提供的数据文件：testata.txt：
000123,12.3,20,123456
000124,12.3,20,123457
000125,12.3,20,123458
000126,12.3,20,123459

（1）生成格式文件，这里提供四种生成方式
基于sql验证的命令行方式：
bcp mydb..testtb format nul -n -fd:\test.fmt -Slocalhost -Usa -Pmypass -t, -r\n
基于信任连接的命令行方式：
bcp mydb..testtb format nul -n -fd:\test.fmt -Slocalhost -T -t, -r\n
在查询分析器实现的sql命令，基于sql验证的：
exec xp_cmdshell 'bcp mydb..testtb format nul -n -fd:\test.fmt -Slocalhost -Usa -Pmypass -t, -r\n'
在查询分析器实现的sql命令，基于信任连接的：
exec xp_cmdshell mydb..testtb format nul -n -fd:\test.fmt -Slocalhost -T -t, -r\n'

这里需要注意的是：使用xp_cmdshell执行操作时，所执行的命令及生成的格式化文件是在数据库所在的服务器上执行的。至于是不是有别的办法可以将格式化文件生成到本地磁盘，我没有能够实现出来，所以不敢妄言。

生成后的格式化文件d:\test.fmt其内容如下，但如果要使用此格式化文件导入文本数据文件到数据库中，则还需要对此数据做些修改才可以。
--------------------------------------------------------------------------
8.0
4
1       SQLCHAR       2       10      ","                       1     productcode       Chinese_PRC_CI_AS
2       SQLNUMERIC    1       19      ","                       2     merchantprice     ""
3       SQLINT        1       4       ","                       3     totalstocks       ""
4       SQLINT        1       4       "\r\n"                    4     productid         ""
---------------------------------------------------------------------------

（2）使用生成的格式化文件导入数据
这里对格式化文件内容做些修改
--------------------------------------------------------------------------
8.0
4
1       SQLCHAR       0       10      ","                       1     productcode       Chinese_PRC_CI_AS
2       SQLCHAR       0       19      ","                       2     merchantprice     Chinese_PRC_CI_AS
3       SQLCHAR       0       10      ","                       3     totalstocks       Chinese_PRC_CI_AS
4       SQLCHAR       0       10      "\r\n"                    4     productid         Chinese_PRC_CI_AS
---------------------------------------------------------------------------
把原来生成的格式化文件中的非sqlchar字段都修改成了sqlchar，并修改前导改为0，另外增加了字符宽度。没有修改之前曾经做过导入，结果发现导入的内容根本不是自己想要的，也许是因为在导入是把对应数据的字符串转成了格式文件要求的数据格式，也就是用二进制的方式读取了数据文件的内容，结果出现问题。在怀疑出现问题的原因后，修改了格式化文件，运行后，数据正常导入，并且相应的数据也做了正确转换。
执行的命令如下：
基于sql验证的命令行方式：
bcp mydb..testtb in d:\testdata.txt -Slocalhost  -Usa -Pmypass -fd:\test.fmt
基于信任连接的命令行方式：
bcp mydb..testtb in d:\testdata.txt -Slocalhost -T -fd:\test.fmt
在查询分析器实现的sql命令，基于sql验证的：
EXEC master..xp_cmdshell 'bcp mydb..testtb in d:\testdata.txt -Slocalhost -Usa -Pmypass -fd:\test.fmt'
在查询分析器实现的sql命令，基于信任连接的：
EXEC master..xp_cmdshell 'bcp mydb..testtb in d:\testdata.txt -Slocalhost -T -fd:\test.fmt'

转载于:https://www.cnblogs.com/mincyw/archive/2008/10/10/1308446.html