今天想导一批文本数据到数据库里

于是就用到了bcp和bulk insert

bcp

其实windows的自带工具，只不过用sql用cmdshell来调用而已

但是sqlserver默认是关闭cmd_shell的，因为安全问题，所以使用时要先打开cmdshell

1 EXEC sp_configure 'show advanced options', 1 2 RECONFIGURE 3 EXEC sp_configure 'xp_cmdshell', 1 4 RECONFIGURE

然后就可以用cmdshell来导入数据了

EXEC master..xp_cmdshell 'BCP database.schema.table in filepath -c -Sservername -Uusername -Ppassword'

用的时候碰到几个问题：

1 如果-S参数不带的话，会默认为本地的数据库服务器，如果本地有多个数据库的话，会报错，要写上具体的服务器名\实例名

2 导入数据时，有的数据格式不对，会造成错误，默认的错误数达到10次，命令就会停止运行，造成后面的数据导入不成功 ，可以使用-m 最大错误数

3 -e这个参数，可以生成错误日志，知道哪些数据没有导入成功，使用时要指定错误文件的完整路径

bulk insert

如果导入的数据中，有分隔符，用bcp就不太好用了，比如字段分隔符为#
这个时候可以用bulk insert，它可以自定义字段分隔符和行终止符，默认的都为/t为字符分隔符，/n为行终止符

BULK INSERT database.schema.table

   FROM 'filepath'

   WITH

     (

        FIELDTERMINATOR = '#',

        ROWTERMINATOR = '/n',

      )

同样bulk insert也有错误数限制，默认为10，可以使用MAXERRORS参数来设定最大错误数

补充点：

如果是bcp导入的话，错误次数超过默认数，导入停止，但是已导入的数据会保存，而bulk insert 错误次数超过默认数，会回滚，一条数据也不会导入

--*******************************************************************************
--                            日期转换参数大全
--********************************************************************************
select CONVERT(varchar,getdate(),120)
--2009-03-15 15:10:02

select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
--20090315151201

select CONVERT(varchar(12) , getdate(), 111)
--2009/03/15

select CONVERT(varchar(12) , getdate(), 112)
--20090315

select CONVERT(varchar(12) , getdate(), 102)
--2009.03.15

select CONVERT(varchar(12) , getdate(), 108)
--15:13:26

其它我不常用的日期格式转换方法:

select CONVERT(varchar(12) , getdate(), 101 )
--03/15/2009

select CONVERT(varchar(12) , getdate(), 103 )
--15/03/2009

select CONVERT(varchar(12) , getdate(), 104 )
--15.03.2009

select CONVERT(varchar(12) , getdate(), 105 )
--15-03-2009

select CONVERT(varchar(12) , getdate(), 106 )
--15 03 2009

select CONVERT(varchar(12) , getdate(), 107 )
-- 15, 2009

select CONVERT(varchar(12) , getdate(), 109 )
--03 15 2009  

select CONVERT(varchar(12) , getdate(), 110 )
--03-15-2009

select CONVERT(varchar(11) , getdate(), 113 )
--15 03 2009 

select CONVERT(varchar(12) , getdate(), 114)
--15:15:55:717