27,579
社区成员
发帖
与我相关
我的任务
分享原汁原味的COPY：BCP实用工具
由于日常基本都是与数据库打交道，我会经常遇到数据转移的需求。类似于：将服务器A的表数据转移到服务器B的同名表中，或将线上服务器的一部分数据拉到线下测试服务器供开发人员调试。通常来说，有以下几种表到表的复制方法：
1.建链接服务器，写INSERT语句。 适用于两台服务器能互相连接，在表的数量比较少的情况下，手动写INSERT语句还是可以接受的。但如果两台服务器无法互联或连接速度较慢，或者多个表需要手写插入列名，写起来会很麻烦，还要调试语句。
2.查询出所有数据或需要导出的数据或用导出工具存成EXCEL，再用导入工具导入目标表。对于数据量不大、比较标准、可以规则的存成EXCEL的数据可以使用这种方法，反之会有各种问题，最常见的就是大文本在导出成EXCEL后经常会无法原样导入，报各种格式错。
3.查出需要导出的数据，复制一下，在SSMS的编辑两200行中选中行复制。这种方法和第二种问题类似，更快一点，但要求也更严。要求更小的数据量，更规则的数据。
4.原库做备份--目标服务器还原备份--跨库插入数据或者直接用还原的库。这种能完全COPY多个表，但缺点一是麻烦，二是需要登陆到服务器上复制备份文件下来，这在管理严密的公司里很可能是要领导签字的。大家懂的，所以我也不常用这种方法。
5.BCP实用工具，也就是本文要介绍的，我现在最常用的数据迁移工具。无视大文本的不规则字符，无视大数据量，通过中间文件传输，不必登陆服务器本机。下面就从最简单的例子入手，介绍一下BCP的用法。
假设源表名T1，数据库名DB1，服务器器实例为SERVER1，目标表名T2，数据库名DB2，服务器实例为SERVER2。T1和T2的结构完全一样。T2中主键与T1不重复或者直接是空表。
首先是导出语句：
BCP DB1.DBO.T1 out d:\T1.dat -S SERVER1 -U sa -P sa -n
在一台能连接SERVER1的装有SQLSERVER的机器上（比如SERVER1本机）的命令行CMD中运行这句命令，就会开始运行导出程序，完成后，会在D盘生成一个名为T1.dat的文件。把这个文件COPY到一台能连接SERVER2，装有SQLSERVER的机器上，运行下面的导入语句：
BCP DB2.DBO.T2 in d:\t1.dat -S SERVER2 -U sa -P sa -n
这样就可以了。
下面就对上面两个语句做一下说明。
BCP是命令，不解释。
DB1.DBO.T1是指定库名、架构名和表名。
out/in是确定方向，out是表导出，in是表导入。out可以改成queryout,对应的前面要改成查询语句，也就是从DB1.DBO.T1改成"SELECT * FROM DB1.DBO.T1 WHERE ...."这样的，实现指定数据导出。
d:\T1.dat是指定文件路径。文件名和后缀名都是随便写的，我只是习惯用这种写法，因为我会经常做多个表的BCP导出，命名规律一点方便通过表名拼BCP语句。
-S -U -P 分别是指定实例名、用户名和密码，权限的话out需要select权限，in需要SELECT和INSERT权限，如果指定了几个特殊选项，比如标识列原样插入，还需要ALTER TABLE权限。
-n是指定格式，这里是指定了使用本机格式（-n），数据会以16进制的形式存储于文件中，因此不存在大文本干扰格式的问题，只要两个表的结构完全一样，一定会成功的。
还有一些其它的格式，如-c，是为了给不同类型的数据库（如MYSQL或ORACLE）导入用的，它会导出成标准的文本格式，但这就会导致一些含乱码和不规则字符的大文本干扰导入，所以我在不关注数据文件内容的时候都是用-n的格式的。
此外还有一些扩展参数，如添加-E后开启标识列原样插入，用于原样复制含有IDENTITY列的表。-b指定批大小，-F和-L指定起始和终止行数。想了解的可以去MSDN的说明上自己了解，我就不多复制了，只提供原文地址：
http://msdn.microsoft.com/zh-cn/library/ms162802.aspx
注意除了BCP和IN/OUT,后面的所有参数都是大小写敏感，-S不要写成-s，-n和-N的含义也不同。
---------------------------------------用分割线，就是这么自信---------------------------------------------------
下面说一下另一种扩展。很多时候，需要导出的表有多个甚至是全库，这时我一般会先用系统表查出表名，或在EXCEL中写入需要的全部表名，然后复制一下，再在CMD中粘贴，就可以实现多表的导入导出了。下面就贴出一段库中全表BCP导出的生成SQL，供大家参考：
SELECT 'BCP DB1.DBO.'+NAME+' OUT D:\'+NAME+'.dat -S SERVER1 -U sa -P sa -n'
,'TRUNCATE TABLE '+NAME
,'BCP DB2.DBO.'+NAME+' in D:\'+NAME+'.dat -S SERVER2 -U sa -P sa -n'
FROM SYS.TABLES
ORDER BY NAME
第一句复制到cmd中导出所有文件，复制文件到能库目标库的机器上，先用TRUNCATE在SSMS中运行清空SERVER2上的所有待导入表，再运行第二个BCP语句导入。我一直是这样做的，在结构一致的情况下，至今没出过问题。
1.建链接服务器，写INSERT语句。 适用于两台服务器能互相连接，在表的数量比较少的情况下，手动写INSERT语句还是可以接受的。但如果两台服务器无法互联或连接速度较慢，或者多个表需要手写插入列名，写起来会很麻烦，还要调试语句。
2.查询出所有数据或需要导出的数据或用导出工具存成EXCEL，再用导入工具导入目标表。对于数据量不大、比较标准、可以规则的存成EXCEL的数据可以使用这种方法，反之会有各种问题，最常见的就是大文本在导出成EXCEL后经常会无法原样导入，报各种格式错。
3.查出需要导出的数据，复制一下，在SSMS的编辑两200行中选中行复制。这种方法和第二种问题类似，更快一点，但要求也更严。要求更小的数据量，更规则的数据。
4.原库做备份--目标服务器还原备份--跨库插入数据或者直接用还原的库。这种能完全COPY多个表，但缺点一是麻烦，二是需要登陆到服务器上复制备份文件下来，这在管理严密的公司里很可能是要领导签字的。大家懂的，所以我也不常用这种方法。
5.BCP实用工具，也就是本文要介绍的，我现在最常用的数据迁移工具。无视大文本的不规则字符，无视大数据量，通过中间文件传输，不必登陆服务器本机。下面就从最简单的例子入手，介绍一下BCP的用法。
假设源表名T1，数据库名DB1，服务器器实例为SERVER1，目标表名T2，数据库名DB2，服务器实例为SERVER2。T1和T2的结构完全一样。T2中主键与T1不重复或者直接是空表。
首先是导出语句：
BCP DB1.DBO.T1 out d:\T1.dat -S SERVER1 -U sa -P sa -n
在一台能连接SERVER1的装有SQLSERVER的机器上（比如SERVER1本机）的命令行CMD中运行这句命令，就会开始运行导出程序，完成后，会在D盘生成一个名为T1.dat的文件。把这个文件COPY到一台能连接SERVER2，装有SQLSERVER的机器上，运行下面的导入语句：
BCP DB2.DBO.T2 in d:\t1.dat -S SERVER2 -U sa -P sa -n
这样就可以了。
下面就对上面两个语句做一下说明。
BCP是命令，不解释。
DB1.DBO.T1是指定库名、架构名和表名。
out/in是确定方向，out是表导出，in是表导入。out可以改成queryout,对应的前面要改成查询语句，也就是从DB1.DBO.T1改成"SELECT * FROM DB1.DBO.T1 WHERE ...."这样的，实现指定数据导出。
d:\T1.dat是指定文件路径。文件名和后缀名都是随便写的，我只是习惯用这种写法，因为我会经常做多个表的BCP导出，命名规律一点方便通过表名拼BCP语句。
-S -U -P 分别是指定实例名、用户名和密码，权限的话out需要select权限，in需要SELECT和INSERT权限，如果指定了几个特殊选项，比如标识列原样插入，还需要ALTER TABLE权限。
-n是指定格式，这里是指定了使用本机格式（-n），数据会以16进制的形式存储于文件中，因此不存在大文本干扰格式的问题，只要两个表的结构完全一样，一定会成功的。
还有一些其它的格式，如-c，是为了给不同类型的数据库（如MYSQL或ORACLE）导入用的，它会导出成标准的文本格式，但这就会导致一些含乱码和不规则字符的大文本干扰导入，所以我在不关注数据文件内容的时候都是用-n的格式的。
此外还有一些扩展参数，如添加-E后开启标识列原样插入，用于原样复制含有IDENTITY列的表。-b指定批大小，-F和-L指定起始和终止行数。想了解的可以去MSDN的说明上自己了解，我就不多复制了，只提供原文地址：
http://msdn.microsoft.com/zh-cn/library/ms162802.aspx
注意除了BCP和IN/OUT,后面的所有参数都是大小写敏感，-S不要写成-s，-n和-N的含义也不同。
---------------------------------------用分割线，就是这么自信---------------------------------------------------
下面说一下另一种扩展。很多时候，需要导出的表有多个甚至是全库，这时我一般会先用系统表查出表名，或在EXCEL中写入需要的全部表名，然后复制一下，再在CMD中粘贴，就可以实现多表的导入导出了。下面就贴出一段库中全表BCP导出的生成SQL，供大家参考：
SELECT 'BCP DB1.DBO.'+NAME+' OUT D:\'+NAME+'.dat -S SERVER1 -U sa -P sa -n'
,'TRUNCATE TABLE '+NAME
,'BCP DB2.DBO.'+NAME+' in D:\'+NAME+'.dat -S SERVER2 -U sa -P sa -n'
FROM SYS.TABLES
ORDER BY NAME
第一句复制到cmd中导出所有文件，复制文件到能库目标库的机器上，先用TRUNCATE在SSMS中运行清空SERVER2上的所有待导入表，再运行第二个BCP语句导入。我一直是这样做的，在结构一致的情况下，至今没出过问题。
...全文
请发表友善的回复…
发表回复
昵称被占用了 2012-09-03
- 打赏
- 举报
zhangyangziwo 2012-09-02
- 打赏
- 举报
谢谢楼主分享，今天刚学习到BCP。
白天的猫头鹰 2012-07-29
- 打赏
- 举报
bcp确实好用
zzzzzkkikkk 2012-07-29
- 打赏
- 举报
好东西，多谢分享啊
LAONINGA098 2012-07-23
- 打赏
- 举报
好东西，多谢分享啊
lavilycn 2012-07-21
- 打赏
- 举报
+1
非常感谢楼主您提供这么好的文章。
非常感谢楼主您提供这么好的文章。
lansedeshuiyan 2012-07-20
- 打赏
- 举报
知道了，试试看吧，谢谢哦
Robert 2012-07-18
- 打赏
- 举报
一直在用的工具，很不错的
evenwang888 2012-07-18
- 打赏
- 举报
非常感谢楼主您提供这么好的文章。
买房动力十足 2012-07-16
- 打赏
- 举报
楼主，非常感谢您提供这么好的文章。
但是我这有两个疑问，想请教下。谢谢。
我也是使用的bcp导出
http://topic.csdn.net/u/20120619/15/c179cc57-707a-4971-8799-471f8440b523.html
这个帖子，是我发的。
其实到现在也没解决。
就是BCP工具使用存储过程的结果，导出到excel文件中。同时表头也到导出。
这个BCP导出的是文本文件吧，其实不是真正的excel文件呢。
谢谢了。
但是我这有两个疑问，想请教下。谢谢。
我也是使用的bcp导出
http://topic.csdn.net/u/20120619/15/c179cc57-707a-4971-8799-471f8440b523.html
这个帖子，是我发的。
其实到现在也没解决。
就是BCP工具使用存储过程的结果，导出到excel文件中。同时表头也到导出。
这个BCP导出的是文本文件吧，其实不是真正的excel文件呢。
谢谢了。
Miracle_Sky 2012-07-16
- 打赏
- 举报
嗯 试了很多参数了 还原的时候还是出现主键重复问题的 还是用Execl弄安全吧。
guguda2008 2012-07-16
- 打赏
- 举报
[Quote=引用 55 楼 的回复:]
BCP 复制的确很强大，但是有个缺点啊，最近在测试使用中的时候发现个问题啊，如果某一列是''这样的数据，那么导出成功后，还原就成了问题。怎么能解决呢？
[/Quote]
加了-n后不存在这个问题。
BCP 复制的确很强大，但是有个缺点啊，最近在测试使用中的时候发现个问题啊，如果某一列是''这样的数据，那么导出成功后，还原就成了问题。怎么能解决呢？
[/Quote]
加了-n后不存在这个问题。
Miracle_Sky 2012-07-16
- 打赏
- 举报
BCP 复制的确很强大，但是有个缺点啊，最近在测试使用中的时候发现个问题啊，如果某一列是''这样的数据，那么导出成功后，还原就成了问题。怎么能解决呢？
helinlin416 2012-07-15
- 打赏
- 举报
工具呢
wwwliyingqi 2012-07-15
- 打赏
- 举报
UP 支持鸭子
huchengchuan 2012-07-15
- 打赏
- 举报
支持支持
Rotel-刘志东 2012-07-14
- 打赏
- 举报
支持一下了。
__lhy 2012-07-14
- 打赏
- 举报
这个工具很不错哦，比我手工写程序，用sqlite来存放数据要强多了，省事省心。
感谢lz分享！
感谢lz分享！
寻道模式 2012-07-13
- 打赏
- 举报
在sql server6.0时代，每次导数据，都是靠BCP的
usestar 2012-07-13
- 打赏
- 举报
少写了一句，EXCEL生成BCP语句
加载更多回复（28）
