欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-02-07 19:50:34
...
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条记录,难道是我操作错误?

相关标签: sql server