- 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)