SQL Server大表数据的导出与导入命令BCP 博客分类： Oracle&MSSQL sql server

SQLServer备份还原命令Sqlcmd，osql，iSql使用：http://lzh166.iteye.com/blog/1123995
SQL Server BCP使用小结：http://www.cnblogs.com/qanholas/archive/2011/07/05/2098616.html
     上面的文章已将有相关命令的使用，我们这边文章主要解决表记录数比较大的情况，比如300万记录，生成SQL脚本文件大小1.6G，用SQL Server Management Studio无法执行文件太大，用navicat跑脚本out of memery，用editPlus编辑，file too large，用UltrEdit编辑，速度慢，想用SQLDumpSplitter分割Sql文件，无效，真实愚蠢的想法。所以只能使用命令了。
1、SQLCMD
SQLCMD 允许在Windows命令窗中通过命令行提示符运行脚本.
语法如下：

C:\Users\donald>sqlcmd -?
Microsoft (R) SQL Server 命令行工具
版本 10.50.1600.1 NT x64
版权所有 (c) Microsoft Corporation。保留所有权利。

用法: Sqlcmd            [-U 登录 ID]          [-P 密码]
  [-S 服务器]            [-H 主机名]          [-E 可信连接]
  [-N 加密连接][-C 信任服务器证书]
  [-d 使用数据库名称] [-l 登录超时值]     [-t 查询超时值]
  [-h 标题]           [-s 列分隔符]      [-w 屏幕宽度]
  [-a 数据包大小]        [-e 回显输入]        [-I 允许带引号的标识符]
  [-c 命令结束]            [-L[c] 列出服务器[清除输出]]
  [-q "命令行查询"]   [-Q "命令行查询" 并退出]
  [-m 错误级别]        [-V 严重级别]     [-W 删除尾随空格]
  [-u unicode 输出]    [-r[0|1] 发送到 stderr 的消息]
  [-i 输入文件]         [-o 输出文件]        [-z 新密码]
  [-f <代码页> | i:<代码页>[,o:<代码页>]] [-Z 新建密码并退出]
  [-k[1|2] 删除[替换]控制字符]
  [-y 可变长度类型显示宽度]
  [-Y 固定长度类型显示宽度]
  [-p[1] 打印统计信息[冒号格式]]
  [-R 使用客户端区域设置]
  [-b 出错时中止批处理]
  [-v 变量 = "值"...]  [-A 专用管理连接]
  [-X[1] 禁用命令、启动脚本、环境变量[并退出]]
  [-x 禁用变量替换]
  [-? 显示语法摘要]

C:\Users\donald>

实例：
执行sql脚本（导入数据）:

sqlcmd  -S  localhost -U sa -P 123456   -i "E:\user.sql"

导入显示sql脚本文件错误，奶奶的不行。

2.OSQL
以下是OSQL命令的参数说明:

C:\Users\donald>osql -?
Microsoft (R) SQL Server 命令行工具
版本 10.50.1600.1 NT x64
版权所有 (c) Microsoft Corporation。保留所有权利。

注意: osql 并不支持 SQL Server 2008 R2的所有功能。
请使用 sqlcmd。有关详细信息，请参阅 SQL Server 联机丛书。
用法: osql                   [-U 登录 ID]          [-P 密码]
  [-S 服务器]                [-H 主机名]           [-E 可信连接]
  [-d 使用数据库名称]        [-l 登录超时值]       [-t 查询超时值]
  [-h 标题]                  [-s 列分隔符]         [-w 列宽]
  [-a 数据包大小]            [-e 回显输入]         [-I 允许带引号的标识符]
  [-L 列出服务器]            [-c 命令结束]         [-D ODBC DSN 名称]
  [-q "命令行查询"]          [-Q "命令行查询" 并退出]
  [-n 删除编号方式]          [-m 错误级别]
  [-r 发送到 stderr 的消息]  [-V 严重级别]
  [-i 输入文件]              [-o 输出文件]
  [-p 打印统计信息]          [-b 出错时中止批处理]
  [-X[1] 禁用命令，[退出的同时显示警告]]
  [-O 使用旧 ISQL 行为禁用下列项]
      <EOF> 批处理
      自动调整控制台宽度
      宽消息
      默认错误级别为 -1 和 1
  [-? 显示语法摘要]

例子:

osql -E  -i "E:\user.sql"

这个导入时间过长，小一点的表可以导入成功，执行过程中遇到如下错误：
对象名 'dbo.test' 无效:http://blog.csdn.net/zhangfy1015/article/details/51690316

3.BCP不利用格式化文件的导入导出命令，用于处理大表数据

C:\Users\donald>bcp -?
usage: bcp [[database_name.]owner.]table_name[:slice_number] {in | out} datafile
        [-m maxerrors] [-f formatfile] [-e errfile]
        [-F firstrow] [-L lastrow] [-b batchsize]
        [-n] [-c] [-t field_terminator] [-r row_terminator]
        [-U username] [-P password] [-I interfaces_file] [-S server]
        [-a display_charset] [-q datafile_charset] [-z language] [-v]
        [-A packet size] [-J client character set]
        [-T text or image size] [-E] [-g id_start_value] [-N] [-X]
        [-M LabelName LabelValue] [-labeled]
        [-K keytab_file] [-R remote_server_principal]
        [-V [security_options]] [-Z security_mechanism] [-Q]

C:\Users\donald>

命令实例：
--导出

bcp test.dbo.user out  E:\user.txt -c -T   

--导入

bcp test.dbo.user  in E:\user.txt -c -T

实战：
--导出数据
(c) 2016 Microsoft Corporation。保留所有权利。

C:\Users\donald>bcp test.dbo.user out  E:\user.txt -c -T

开始复制...

已复制 2648040 行。
网络数据包大小(字节): 4096
总时钟时间(毫秒)     : 25643   平均值: (每秒 105921.56 行。)
生成的txt文件只有400M左右。

--导入数据

C:\Users\donald>bcp test.dbo.user  in E:\user.txt -c -T

开始复制...
..
已向 SQL Server 发送了 1000 行。总共发送了: 2643000
已向 SQL Server 发送了 1000 行。总共发送了: 2644000
已向 SQL Server 发送了 1000 行。总共发送了: 2645000
SQLState = 22003, NativeError = 0
...
Error = [Microsoft][SQL Server Native Client 10.0]数值超出范围
已向 SQL Server 发送了 1000 行。总共发送了: 2646000
已向 SQL Server 发送了 1000 行。总共发送了: 2647000
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]字符串数据，右截断

已复制 2648040 行。
网络数据包大小(字节): 4096
总时钟时间(毫秒)     : 52196   平均值: (每秒 50738.46 行。)

C:\Users\donald>
遇到如下错误
CTLIB Message:  - L6/O8/S5/N3/5/0:
ct_connect(): directory service layer: internal directory control layer error: Requested server name not found.
Establishing connection failed.
解决方式：
http://blog.sina.com.cn/s/blog_5ceb51480101gs4j.html
---分析原因
我的系统中之前安装了Sybase数据库，而SQLSERVER数据库是在Sybase之后装的，
这样就导致了SQLSERVER对应的环境变量在Sybase之后了，在执行BCP命令时，
系统首先找到的BCP是Sybase的BCP，而不是SQLSERVER的。用Sybase的BCP往SQLSERVER中导数据当然会有问题。
----解决方法
在PATH环境变量中将所有涉及SQL Server的环境变量提到Sybase之前，这样就可以了。
看来windows的环境变量也有先后优先级之分，在之后的应用中得引以注意。
总结：
对于大表数据的导入与导出用BCP命令，不利用格式化文件的导入导出数据；
OSQL主要可以处理SQL脚本文件类型的数据。bcp可能会有数据丢失呀，我测试的每个表丢失了1条记录，难道是我操作错误？