SQL SERVER几种数据迁移/导出导入的实践

SQLServer提供了多种数据导出导入的工具和方法，在此，分享我实践的经验（只涉及数据库与Excel、数据库与文本文件、数据库与数据库之间的导出导入）。

（一）数据库与Excel

方法1：

使用数据库客户端（SSMS）的界面工具。右键选择要导出数据的数据库，选择“任务”——“导出数据”，下图1，按照向导一步一步操作即可。而导入则相反，导入时，SQLServer会默认创建一张新表，字段名也默认跟导入的Excel标题一样，并且会默认字段数据类型等。当然在可以在向导进行修改。需要注意的是如果标题不是英文而是中文，默认创建字段名也是中文，这将给后面数据更新操作带来麻烦，所以最好还是以有意义的英文字段名。把数据导入后，再通过执行语句，把数据插入/更新到业务表。

 figure-1：任务——导出数据

 

方法2：

从SQLServer2005开始，可以直接在SSMS上查询出来的结果复制，然后粘贴到Excel上，对于少量数据来说，是非常快速方便的，需要注意的是长数字可能会变成科学记数法的形式，提前在Excel上指定列的格式为文本即可。

导入的话，ctrl + c 复制Excel上的数据，然后在选择相关表，编辑数据，把数据直接粘贴上去即可。但是不建议直接粘贴到业务表（如果表是空白没有数据，并且字段顺序对应，可以这样操作），而是建议先粘贴到一个新建的中间表中，然后再通过语句，把数据插入/更新到业务表。

这种方法的导出导入，适合于少量的数据，如5000行以内的记录，大于5000行以上就不建议了，速度较慢，如果数据过大，还一定成功。

 

 

（二）数据库与文本文件、数据库与数据库

数据库之间的数据迁移或导出导入其实是比较方便的，比如备份数据库后，在新的机器上做恢复。但是需要注意的是SQL2008之前的版本的备份无法在SQL2012或以上版本上直接恢复的，而是通过中间的SQL2008做一个过渡，把旧版本的数据库恢复到SQL2008，然后做备份，最后在SQL2012上恢复。

如果是新版本（下面以SQL2012为例）的备份文件恢复到旧版本（以SQL2008为例）上就比较麻烦了，一般是不支持新版本备份文件在旧版本中恢复的。只能通过编写脚本，把新版本的数据导入到旧版本中。

 

方法1：

首先推荐使用的是数据不落地的“链接服务器”。使用SQL2012的SSMS，同时连接到SQL2012和SQL2008的实例，通过编写脚本把SQL2012的数据导入到SQL2008中。两个实例的可以通过链接服务器来连接。以下是设置步骤。

figure-2：新建链接服务器

 

figure-3：链接服务器和数据源

 

figure-4：认证

 

figure-5：创建成功后，可以直接浏览链接服务器的目录，也可以使用语句查询了。

 

也可以使用脚本来创建链接服务器。

--创建链接服务器
EXEC sp_addlinkedserver 
@server='LINKED_SERVER_TEST2',--被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='192.168.88.6,11433'--数据源
GO

--创建登录名和密码
EXEC sys.sp_addlinkedsrvlogin
@rmtsrvname = 'LINKED_SERVER_TEST2', -- 被访问的服务器别名
@useself = 'false',
@locallogin = NULL,
@rmtuser = 'sa', -- 数据源登录名
@rmtpassword = 'psd123456' -- 数据源登录密码
GO

--设置数据可以访问
EXEC sys.sp_serveroption
@server = 'LINKED_SERVER_TEST2', 
@optname = 'data access',
@optvalue = N'true'
GO

code-1：创建链接服务器的脚本

 

创建成功后，可以直接查询数据。

figure-6：查询链接服务器的数据

 

通过视图sys.servers可以查询所有服务器及相关的属性。

figure-7：查询所有链接服务器

 

在SSMS上或运行以下脚本可以删除指定的链接服务器。

--删除链接服务器及所有登录
EXEC sys.sp_dropserver
 @server = 'LINKED_SERVER_TEST2',
 @droplogins = 'droplogins'
 GO

 code-2：删除链接服务器及所有登录

 

详细请参考：https://technet.microsoft.com/zh-cn/library/ff772782%28v=sql.105%29.aspx

 

 

方法2：

如果两个实例不能连接，只能在SQL2012上导出数据，再到SQL2008上导入。SQLServer提供生成包含数据的脚本工具，下图2。在第三步的“高级”选项里有一项“Types of data to scripts”有三个选择：Data only，Schema and data，Schema only，分别是只生成数据、生成表（对象）和数据，表（对象）。还有生成脚本的版本“Script for Server Version”，下图3。其他选项，按实际需要选择。

 

 figure-8：任务——生成脚本

 

figure-9：生成脚本的高级选项

 

也可以使用存储过程生成包含数据的脚本。这里介绍一个别人已经做写好存储过程：sp_generate_inserts。运行之后，会按表每条记录生成一条insert的语句

 View Code

code-3：sp_generate_inserts脚本源代码

 

在我的实际使用中，只有两三个参数比较常用，分别是@table_name、@from和@owner，如果表的架构使用默认的dbo，则可以省略。以下是一个使用的例子：

figure-10：使用sp_generate_inserts的一个例子

 

其他参数的用法，这里就不一一解释了。我经常使用这个存储过程做一些简单而少量（如数万行记录以内）的数据导出导入，比前面介绍的方法方便快捷许多。但这个存储过程支持处理一般常用的数据类型，像XML这种类型则不支持。还有，如果生成的数据太多太大，SSMS返回数据会很慢，甚至SSMS会挂了，这时还是使用SSMS自带的导出脚本到文件稳妥些。如果使用生成的数据脚本文件很大，几百MB甚至上GB，在导入时，就不能直接使用SSMS直接打开来执行了。可以使用SQLCMD实用工具来在执行脚本。如下面的一个例子，在D盘下有一个脚本1.sql，内容为：

USE AdventureWorks2008R2
GO
SELECT * FROM Person.CountryRegion;
GO

code-4：SQLMCD的测试脚本

 

在运行下输入CMD，输入：

sqlcmd -S localhost -d AdventureWorks2008R2 -i D:\1.sql

code-5：SQLMCD的命令

 

回车执行后如下图，SQLCMD的详细用法，请参考：https://msdn.microsoft.com/zh-cn/library/ms180944.aspx

和 https://msdn.microsoft.com/zh-cn/library/ms162773%28v=sql.105%29.aspx

 

 figure-11：SQLCMD的测试例子

 

 

方法3：

使用BCP导出导入大容量数据。可以参阅我的另一篇博客《BCP导出导入大容量数据实践》。

 

以上几种方法是我在日常工作比较常使用的数据导出导入的工具，每一种方法都有各自的优势和不同的使用场景，使用不同的方法组合，可以节省不少时间，提高工作效率，希望对您的有所帮助。如果您有更好的建议或方法欢迎告诉我！

 

 

 

标签: sqlserver, 导出导入