5.如何使用常用的6种方式对数据进行转换（二）

实验目标：

1、 使用T-SQL语句对数据进行转换。

2、 使用备份和还原对数据进行转换。

3、 使用分离和附加对数据进行转换。

4、 使用复制对数据进行转换。（重点讲解）

5、 使用SSIS导入和导出向导对数据进行转换。

6、 使用SSIS包设计器对数据进行转换

 

 

实验步骤：

注意：下面所有的操作都将对以下表格进行更改

 

4、 使用复制对数据进行转换。

通过复制可以将数据的多个拷贝分发到公司中的各个服务器中。通过复制为多台服务器提供相同的数据，这样用户就可以在不同服务器中访问同样的信息，对以一个拥有大量用户的企业，复制可以分散用户访问服务器的负载，从而为每个用户提供更高质量的服务。

复制可以将数据和数据库对象从一个数据库复制和分发到另一个数据库，然后在数据库间进行同步，以维持一致性。使用复制，可以在局域网和广域网、拨号连接、无线连接和Internet上将数据分发到不同位置以及分发给远程或移动用户。

发布服务器拥有原始数据的拷贝，通过订阅，将数据送到分发服务器，并且由分发服务器传递给订阅服务器。

分发数据库存储复制状态数据和有关发布的信息，并且在某些情况下为从发布服务器向订阅服务器移动的数据起着排队的作用。在很多情况下，一个数据库服务器实例可以充当发布服务器和分发服务器两个角色。

订阅服务器拥有数据的拷贝，根据所选复制的类型，订阅服务器还可以将数据更改传递回发布服务器或者将数据重新发布到其他订阅服务器。

 

实验环境：

在某一个公司里的mysql数据库中，有很多应用与销售信息记录表（mytable表）相关联，作为这家公司的数据库管理员，为了避免冲突，需要将mysql表复制到一个不同的SQL Server实例上，原实例中的表用于数据更新，副本则用于查询分析，并且需要时保持同步更新。

 

实施步骤：

1、 首先安装两台SQL，如果都在工作组环境下，就用混合模式进行验证，如果都在域环境下，就用windows域用户账户进行验证。本实验是在工作组环境下，为了简便起见都用SQL Server中的SA用户进行验证，实际中，最好不要这样去做。如何启用SA账户这里就不做过多介绍了。

首选连接到其中一台发布服务器上，然后选择“新建发布”就可以了，注意：发布服务器必须要开启SQL Server Agent服务。其次，服务器实例必须为本机计算机名称。

 

选择其中一个需要发布的数据库mysql。

 

发布类型：

快照复制—这是最容易进行设置的复制方式，快照复制只需要周期性地发送数据。当同步发生时，快照复制发送所有数据。不需要去监视数据的修改，但这样也可能引起数据流量的增加，如：当没有数据修改时也会发送所有的数据。

事务性复制—与快照复制不同，只要数据发生改变。事务性复制就将它们发送给订阅服务器。这样可以减少数据复制的流量。事务性复制通常从发布数据库对象和数据的快照开始，创建初始快照。当发生复制时，事务日志被发送到订阅服务器上，用以保证事务性的一致性。默认情况下，事务性发布的订阅服务器被视为只读，因为更改将不会传播回发布服务器。

合并复制—合并复制允许既可以在发布服务器上修改数据，也可以在订阅服务器上修改数据，当同步发生时，发布服务器的修改和订阅服务器的修改被合并在一起。在合并复制中，同一数据可能由发布服务器和多个订阅服务器进行了更新。因此在合并更新时可能会产生冲突，合并复制提供了多种处理冲突的方法。与事务复制相同，合并复制通常也是从发布数据库对象和数据的快照开始，当复制发生时，之后的更改被合并在一起。

为了满足实验背景需求，采用“事务性发布”。

 

选择需要发布的表，本实验选用mytable，注意，选择的表中必须要有主键才可以。

 

可以通过下面的“添加”菜单对需要表中的有效信息进行筛选，本实验就不筛选了。

 

指定连接发布服务器的账户，本实验由于在工作组中，所以选用SQL Server内置账户。

 

给该发布服务器命一个名称。

 

发布创建成功之后，会在本地发布子目录里显示出发布的数据库。

 

通过本机连接到另一台SQL Server数据库实例上，通过本地订阅，订阅发布的内容。也可以在另一他SQL Server数据库上做。

 

选择“发布的服务器”然后通过SA账号连接到发布服务器上。订阅自己需要的发布内容（在实际环境中可能有多个发布）。

 

在实际环境中发布服务器和分发服务器一般放在同一个SQL server中，就选择最上面那个就可以了。

 

添加订阅服务器到此列表中，并选择需要订阅的服务器，然后选择订阅服务器中的一个数据库就可以了。

 

与分发服务器的连接需用模拟进程账号，这样做不是很安全，如果是域环境就直接使用域用户账户就可以了，与订阅服务器的连接，使用订阅服务器上的SQL 账户，这里选用SA，实际中，决定不允许使用这个账户。

 

完成之后，可以在订阅服务器的mysql_dingyue数据库中看到刚才订阅的表。

 

为了测试方便，在发布服务器上修改数据库mysql中的表mytable，将小诺同志的工资加1000，看订阅服务器是否发生变化。

 

使用SQL语句查看订阅服务器数据库mysql_dingyue的表mytable看小诺的工资是否发生变化。

 

如果测试不成功，可以通过“查看同步状态”进行坚持数据库的状态。

 

5、 使用SSIS导入和导出向导对数据进行转换。

导入和导出向导提出了一种从源向目标复制数据的最简便的方法，可以在多种常用数据格式之间转换数据，还可以创建目标数据库和插入表。

可以向下列源中复制数据或从其中复制数据：SQL Server、文本文件、Access、Excel、其他OLE DB访问接口。

这些数据源即可用作源，又可用作目标。还可将ADO.NET访问接口用作源。指定源和目标后，便可选择要导入或导出的数据。可以根据源和目标类型，设置不同的向导选项。例如，如果在SQL Server数据库之间复制数据，则指定要从中复制数据的表，或提供用来选择数据的SQL 语句。

实验内容：

1、将SQL Server数据导出到Excel中

2、将Access数据导入到SQL Server中

实验步骤：

********将SQL Server数据导出到Excel中************

例如：将数据库mysel中的mytable中的Name和Salary>4000数据导出到一个名为mytable_excel中

 

数据源选择SQL Native Client 服务器名称选择本地服务器，并且使用Windows身份验证，也可以使用混合模式身份验证，数据库选择mysql

 

目标数据选择Microsoft Excel，并通过浏览指向需要导出到的那个excel表格。

 

由于不是全部导出，需要通过命令行对数据库中的表进行赛选。

 

输入语句select name，salary from myatable where salary >4000，意思就是将表mytable中name和salary>4000的数据导出来。写完语句之后，最好通过分析验证一下。然后再执行一下看看是否是你想要的结果，如果不是再返回上一步继续操作。

 

下面是导出的数据，完全符合标准。

 

*******************将Access中数据导入到SQL Server中***************************

例如：将Access数据库mytable_access中的数据导入到SQL Server数据库mysql中的test表中。

下面是源数据

 

源数据选择Microsoft Acces，并浏览到源数据文件即可。

 

目标数据选择SQL Native Client即可，数据库选择mysql

 

如果源数据和目标数据有差异，做一个列映射，在SQL Server中列名最好用英文字母表示。并先前在SQL Server数据库mysql中创建一张与Access数据库同名的表。导入之后，数据就导入到那张同名的表中。

 

6、 使用SSIS包设计器对数据进行转换（推荐使用）

使用SSIS设计器可以将先前做的导入和导出以及其它操作做成一个包的形式存放，需要导入或者导出数据时，只需要将包执行一次就可以了，而且转换速度是先前速度的7倍。

简单了解一下包的概念：包是一个集合，其中可包括连接、控制流、数据流、事件处理程序、变量和配置，可以使用SSIS工具将这些对象组合到包中。包是最重要的Integration services对象。

*******************将SQL Server数据导出到Excel中******************************

例如：将数据库mysql中的数据库mytable导出到一个名为newtest.xls中

打开开发工具SQL Server Business Interlligence Development Studio.lnk。，然后新建一个项目。

 

在解决方案资源管理中选择“数据源”，新建一个数据源，如果哦解决方案资源管理器未出现，单击菜单栏中的“视图”—》“解决方案资源管理器”

 

为mysql数据源建立连接管理器，在连接管理器窗口中，单击鼠标右键，选择“从数据源新建连接”。

 

建立数据流任务，在包设计窗口中单击“控制流”标签。在工具箱中选择“数据流任务”并拖动到包设计器窗口。然后单击“控制器”标签，选择“工具箱“中的””“数据流源”，然后选择“OLE DB源”，并拖动到包设计器窗口。

 

在包设计窗口中选择“OLE DB源”，单击鼠标右键，选择“编辑”，在弹出的对话框中指定下列表

 

然后，选择“工具箱”中的“数据流转换”，选择“派生列”并拖动到包设计窗口。在包设计窗口拖动“OLE DB源”的绿色连接到“派生列”。

然后在工具箱中选择“数据库目标里的“平面文件目标”并拖动到包设计窗口。在包设计窗口中拖动“派生列”的绿色连接到“平面文件目标”然后编辑，如下图所示：

 

在目标编辑器中选择“映射”并将mytable表中的列映射到ACCESS表中。

 

设置完成之后，可以先测试一下，然后保持。

 

保存的SSIS包在下面路径里，需要时，双击这个包就可以了

 

下面是SQL SERVER里的mysql里的表mytable转换成Access表的结果。

 

*******将Access中数据导入到SQL Server中*******

例如：将Access数据库mytable_access中的数据导入到SQL Server数据库mysql中的xiaonuo表中。

做法与上面基本相同。这里就不做过多演示了。

本文出自 “ 【小诺滴网络技术课堂】” 博客，请务必保留此出处 http://dreamfire.blog.51cto.com/418026/155126

本文出自 51CTO.COM技术博客

转载于:https://blog.51cto.com/973503/210978