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的语句
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存储过程的导出导入 近日在研究SQL的存储过程,这里我陆续将我在使用存储过程中碰到的问题及解决办法发布到网上,请各位大虾指正。 SqlServer存储过程的导出导入,网上相关资料特别少,经过摸索,这里写...
精简版SQL Server 2005 数据库导出、导入办法 应该有用~
在SQL Server数据库之间进行数据导入导出 (1).使用SELECT INTO导出数据 在SQL Server中使用最广泛的就是通过SELECT INTO语句导出数据,SELECT INTO语句同时具备两个功能:根据SELECT后跟的字段以及INTO后面跟...
基于SQLServer2005 Integration Service的数据导入导出的案例, XML源->数据表->Excel文件,使用数据转换组件,XML组件,Excel 文件组件,利用事务控制和隔离机制管理多个组件件的数据同步
c# 编写的winform 操作sql server数据库操作源程序,程序设计技巧性强,集sql server数据库操作之大全,跨窗体增加,修改数据,可连接,显示,修改,查询,导入/导出EXECL。可访问本地数据库,也可访问局域网数据库...
SQL Server三种导入导出数据方式比较
SQL SERVER 和EXCEL的数据导入导出,开关xp_cmdshell
SQLServer 异构数据库之间数据的导入导出SQLServer 异构数据库之间数据的导入导出
bcp是微软SQLServer数据库系统的命令行工具,用于进行大数据量的导入导出,该工具简单实用、效率极高,本文总结了bcp命令操作的各种设置开关功能含义和使用方法,可以为需要的用户提供有益帮助。
C# SQL SERVER 脚本导出导入 ; SQL SERVER 导出;SQL SERVER 导入;SQL SERVER 数据库 导出;SQL SERVER 数据库 导入; 实现功能 和SQL Server 2014 Management Studio 里面的生成脚本相似 介绍地址:...
SQL SERVER数据导出工具SQL SERVER数据导出工具SQL SERVER数据导出工具SQL SERVER数据导出工具SQL SERVER数据导出工具SQL SERVER数据导出工具SQL SERVER数据导出工具
可以将数据库中的数据导出insert into脚本, sql server2008以上版本已经自带导出数据库数据成insert脚本功能,该工具针对2005以下版本.使用时先选择数据库服务器地址,在右侧选择Sql Server login,输入数据库账号...
关于SqlServer通过脚本访问Excel数据,以及通过企业管理器导入Excel数据问题解决办法。
对SQLSERVER单个表的部分查询结果进行导出至EXCEL,并由EXCEL导入至数据库表中。包含同一服务器中的数据库表之间的通过SQL语句进行导出导入,及不同服务器之间的数据库表之间的导出导入
介绍SQL_Server数据与Excel表的导入导出方法
导出导入SQL Server里某个数据库 导出导入SQL Server里某个表
基于OPENDATASOURCE函数的SQL Server数据库海量数据的快速导入导出方法
sql server 和excel的数据导入导出.sql server 和excel的数据导入导出.
SQL数据导入/导出故障.pdf
此软件可以顺利的将数据库中的所有数据导出成为sql语句,用于数据导入导出是个不错的工具