BCP导入数据有关问题

2012-03-08

BCP导入数据问题SQL codeEXEC master..xp_cmdshell bcp test.dbo.book1 in D:\test\2010-3-2.txt -t\t-

BCP导入数据问题

SQL code

EXEC master..xp_cmdshell 'bcp test.dbo.book1 in D:\test\2010-3-2.txt -t"\t"  -c  -S"127.0.0.1" -U"sa" -P"30687724"'

数据库表《bcp test.dbo.book1》的数据类型全部为“VARCHAR(50)” 就上面的语句是可以执行导入的！

因为我考虑到数据入库后需要计算，我就把原表《bcp test.dbo.book1》的部分列的数据类型改为“date”、“INT”时就不能导入了，提示如下：
麻烦帮下，在线等，谢谢！！

SQL code

NULL开始复制...SQLState = 22005, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]对于造型说明无效的字符值SQLState = 22005, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]对于造型说明无效的字符值SQLState = 22005, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]对于造型说明无效的字符值SQLState = 22005, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]对于造型说明无效的字符值SQLState = 22005, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]对于造型说明无效的字符值SQLState = 22005, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]对于造型说明无效的字符值SQLState = 22005, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]对于造型说明无效的字符值SQLState = 22005, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]对于造型说明无效的字符值SQLState = 22005, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]对于造型说明无效的字符值SQLState = 22005, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]对于造型说明无效的字符值NULLBCP 复制 in 失败NULL

[解决办法]
用bulk insert导入即可,以前5列数据为例,

SQL code

-- 建导入的文件D:\0110.txt 内容:日期时间 CGI BSC TCH话务量2010-3-3 0 460-00-32808-39011 BSC07 4.2406959532010-3-3 0 460-00-32808-39012 BSC07 2.32038331-- 建测试表create table book1(a date,b int,c varchar(20), d varchar(10), e decimal(10,7))-- bulk insert导入bulk insert DBAP.dbo.book1from 'D:\0110.txt'with ( FIELDTERMINATOR =' ', -- 分隔符 ROWTERMINATOR ='\n', KILOBYTES_PER_BATCH=5000, firstrow=2 )select * from book1a b c d e---------- ----------- -------------------- ---------- ---------------------------------------2010-03-03 0 460-00-32808-39011 BSC07 4.24069602010-03-03 0 460-00-32808-39012 BSC07 2.3203833(2 row(s) affected)

热点排行