使用bcp进行大数据量导出导入

导读：数据的导入导出，相信很多人都非常熟悉，可是当涉及到大量数据的导入导出时，就会出现很多的问题，下面就为大家讲解一种能够实现大量数据导入导出的方法，就是使用BCP的方案。

SQL Server的导出导入方式有：

1、在SQL Server中提供了导入导出的界面操作。
2、在界面操作中又分【复制一个或多个表或视图的数据】和【编写查询以指定要传输的数据】两种模式，第一种是直接对表、视图进行全部字段、记录进行导出，而第二种就是可以通过SQL语句来控制导出导入的字段和行。
3、使用简单但有用的SQL脚本中的【表复制】这里面的方法。

4、再一种就是在命令行中使用bcp命令来导入导出数据，需要特别说明的是，这是对大数据量导入导出就好的办法。下面是我实践出来可以使用的脚本，但是我在使用bcp的时候遇到了一些问题，希望大家给于帮助。谢谢。（PS：问题在下面代码中）使用xp_cmdshell的时候可能会有些问题，可以参考：MSsql2005 如何启用xp_cmdshell <!–[endif]–>
还没有解决的问题
1：使用BCP命令时如果导出的字段为空想替换为其它描述（比如替换成NULL）如何办？
2：使用 case when 时就会出现1023字节的问题，那有没其它办法解决呢？
3：使用BCP命令导出大数据量时出现错误：查询提示超过了最大目录缓冲区 1023 字节（2164 字节输入）。
备注
1：已经使用了Coalesce代替case when，但是还是超出字符数了。
2：对以网上说的放到Temp数据库中、中间表，这些？但是数据表几十G了，还能这样？

整个表导出(out)
bcp 数据库名.dbo.表名 out c:\currency.txt -S”数据库实例” -U”用户” -P”密码” -c

使用SQL语句导出(queryout)
bcp “select * from 数据库名.dbo.表名” queryout c:\currency.txt -S 数据库实例 -U”用户” -P”密码” -c

设置字段分隔符和行分隔符(-c -t”,” -r”\n”),不想输入字段类型等请配合-c一起使用
bcp “select * from 数据库名.dbo.表名” queryout c:\currency.txt -S 数据库实例 -U”用户” -P”密码” -c -t”,” -r”\n”

指定每批导入数据的行数、指定服务器发出或接收的每个网络数据包的字节数(-k -b5000 -a65535)
bcp “select * from 数据库名.dbo.表名” queryout c:\currency.txt -S 数据库实例 -U”用户” -P”密码” -c -t”,” -r”\n” -k -b5000 -a65535

在查询分析器上执行(EXEC master..xp_cmdshell)
EXEC master..xp_cmdshell ‘bcp “select * from 数据库名.dbo.表名” queryout c:\currency.txt -S 数据库实例 -U”用户” -P”密码” -c’

把SQL语句生成一个.sql文件，然后调用
注：路径的文件夹名称中间不能有空格
exec master..xp_cmdshell ‘osql -S 数据库实例 -U 用户 -P 密码 -i C:\cmdshellTest.sql’

将数据导入到currency表中
EXEC master..xp_cmdshell ‘bcp 数据库名.dbo.表名 in c:\currency.txt -c -T’
导入数据也同样可以使用-F和-L选项来选择导入数据的记录行。
EXEC master..xp_cmdshell ‘bcp 数据库名.dbo.表名 in c:\currency.txt -c -F 10 -L 13 -T’
在使用命令xp_cmdshell的时候需要设置权限：

/*MSsql2005 如何启用xp_cmdshell
默认情况下,sql server2005安装完后,xp_cmdshell是禁用的(可能是安全考虑),如果要使用它,可按以下步骤
*/
允许配置高级选项
EXEC sp_configure ‘show advanced options’, 1
GO
重新配置
RECONFIGURE
GO
启用xp_cmdshell
EXEC sp_configure ‘xp_cmdshell’, 1
GO
重新配置
RECONFIGURE
GO

执行想要的xp_cmdshell语句
Exec xp_cmdshell ‘query user’
GO

允许配置高级选项
EXEC sp_configure ‘show advanced options’, 1
GO
重新配置
RECONFIGURE
GO
禁用xp_cmdshell
EXEC sp_configure ‘xp_cmdshell’, 0
GO
重新配置
RECONFIGURE
GO
或者使用图形界面：

应用上文中介绍的方法就能够轻松实现使用bcp进行大数据量导出导入，希望大家通过上文的学习，都能够很熟练的掌握这个技巧，相信这在您以后工作中遇到类似问题时会非常有帮助的。

【编辑推荐】