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语句导入。我一直是这样做的,在结构一致的情况下,至今没出过问题。
假设源表名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语句导入。我一直是这样做的,在结构一致的情况下,至今没出过问题。