Microsoft SQL Server 数据量大 导入导出 问题汇总

问题一:

今天拿到一份有近百万条数据的Excel要导到数据库里面，我先在本地(2014)用自带Excel，然后生成脚本文件去服务器(2008)上执行；文件SQL打开不了。

解决方法:

用自带的sqlcmd工具

第一步：Win+R 键入：cmd 命令，开启命令行工具；

第二步：键入：cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn （具体目录路径跟你安装的SQL位置有关）

第三步：键入：sqlcmd -S . -U sa -P 123 -d test -i data.sql

参数说明：-S 服务器地址 -U 用户名 -P 密码  -d 数据库名称 -i 脚本文件路径 

转自:https://www.cnblogs.com/zhangjbravo/p/7059099.html

更多参考：https://msdn.microsoft.com/zh-cn/library/ms162773(v=sql.120).aspx

 

问题二：

今天拿到一个数据库备份文件，服务器上还原一下，一百来万条数据，要导出成Excel，一套 任务 => 导出数据 => SQL->Excel 2007 很好导出到65536条报错，一查是Excel限制 。到本地试试还是不行，那么写存储过程用分页导出多张子表，每个子表65536条数据。好烦不想写。用bcp吧。

解决方法：

第一步：运行下列语句设置安全配置

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1

RECONFIGURE

没设置会报错“SQL Server 阻止了对组件 'xp_cmdshell' 的 过程'sys.xp_cmdshell' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'xp_cmdshell'。有关启用 'xp_cmdshell' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器". ”

第二步：EXEC master..xp_cmdshell 'bcp "use -B select * from -T queryout -E -c -S -U -P

参数说明：-B 数据库名称  -T 表名  -E Excel路径  -S 服务器地址  -U 用户名  -P 密码 

例子：EXEC master..xp_cmdshell 'bcp "use LJ_PD_IPR_INTERFACE select * from PatentInformation" queryout D:\11.xls -c -S"." -U"sa" -P"123456"'

 

问题三：

用bcp，导出的数据中含有“【”、“】”等特殊字符时，最后Excel的样式会错乱。又寻到个工具：SQL Prompt

 

下载地址:https://www.red-gate.com/products/sql-development/sql-prompt/

使用方法很简单，速度也很快，先查询出所需要的数据，右击左上角的open in excel就好了