使用TLSQL实现导出导出支持EXCLE的导入导出.docx

1、使用TLSQL实现导出导出支持EXCLE的导入导出使用T-SQL实现数据导出导入（SQLSERVERSQLSERVER） 2007-07-10 16:12:08标签：数据 Transact-SQL 今天尝试使用Transact-SQL进行数据的导出导入，收获颇丰。与使用DTS相比，效率要高很多！一、打开OPENDATASOURCE功能开始 所有程序 Microsoft SQL Server 2005 配置工具 SQL Server外围应用配置器 功能的外围应用配置器 实例名 Database Engine 即席远程查询 启用OpenRowset和OpenDatasource支持。否则，会报错：

2、消息 15281，级别 16，状态 1，第 1 行SQL Server 阻止了对组件 Ad Hoc Distributed Queries 的 STATEMENTOpenRowset/OpenDatasource 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 Ad Hoc Distributed Queries。有关启用 Ad Hoc Distributed Queries 的详细信息，请参阅 SQL Server 联机丛书中的 外围应用配置器。 二、打开远程连接开始 所有程序 Microsoft SQL Server 200

3、5 配置工具 SQL Server外围应用配置器 服务和连接的外围应用配置器 实例名 Database Engine 远程连接 本地连接和远程连接 仅使用TCP/IP 应用 重新启动数据库引擎否则，会报错：链接服务器(null)的 OLE DB 访问接口 SQLNCLI 返回了消息 登录超时已过期。链接服务器(null)的 OLE DB 访问接口 SQLNCLI 返回了消息 建立到服务器的连接时发生错误。连接到 SQL Server 2005 时，默认设置 SQL Server 不允许远程连接这个事实可能会导致失败。消息 65535，级别 16，状态 1，第 0 行SQL 网络接口: 从注册表

4、获取已启用的协议列表时出错 xFFFFFFFF. 三、打开客户端TCP/IP协议开始 所有程序 Microsoft SQL Server 2005 配置工具 SQL Server Configuration Manager SQL Server 2005网络配置：启用TCP/IP 协议SQL Native Client 配置： 启用 默认端口-1433四、创建联接服务器exec sp_addlinkedserver server=conndb04, provider=sqloledb, srvproduct=, datasrc=WINNIEIIDCexec sp_addlinkedsrvlog

5、in rmtsrvname=WINNIEIIDC, useself=false, locallogin=admin, rmtuser=admin, rmtpassword=password;注这里的login帐户，应该是使用SQL Server身份验证的帐户，否则，会报错：xx用户无法登陆。五、在导入目标数据库中创建空表 use wwww go create table pro_unit_sort( CulID varchar(40), Coll_Unit varchar(400), cul_Sort varchar(400)六、执行导出导入select * into wwww.dbo.pro

6、_unit_sort from conndb04.wwww_xx.dbo.xx._unit_sort; 注 1、第四步和第六步等同于： use wwww go insert INTO OPENDATASOURCE( SQLOLEDB, Data Source=WINNIEIIDC; User ID=admin; Password=password ).wwww.dbo.pro_unit_sort select * from wwww_xx.dbo.xx.unit_sort; 2、执行此操作时，将使用到UDP 1434端口，如果防火墙关闭了此端口的话，请将其打开。否则，会报错：链接服务器(nul

7、l)的 OLE DB 访问接口 SQLNCLI 返回了消息 登录超时已过期。链接服务器(null)的 OLE DB 访问接口 SQLNCLI 返回了消息 建立到服务器的连接时发生错误。连接到 SQL Server 2005 时，默认设置 SQL Server 不允许远程连接这个事实可能会导致失败。消息 65535，级别 16，状态 1，第 0 行SQL 网络接口: 从注册表获取已启用的协议列表时出错 xFFFFFFFF. 参考文档：1、使用Transact-SQL进行数据导入导出方法详解： 2、SQLServer的链接服务器技术小结 如何将数据从 Excel 导入到 SQL Server查看本

8、文应用于的产品本页 概要 o 技术说明 o 要求 o 示例 导入与追加 使用 DTS 或 SSIS 使用链接服务器 使用分布式查询 使用 ADO 和 SQLOLEDB 使用 ADO 和 Jet Provider o 疑难解答 参考 展开全部 | 关闭全部 概要 本文循序渐进地演示如何用不同的方法将数据从 Microsoft Excel 工作表导入到 Microsoft SQL Server 数据库。技术说明本文中的示例.本文循序渐进地演示如何用不同的方法将数据从 Microsoft Excel 工作表导入到 Microsoft SQL Server 数据库。回到顶端技术说明本文中的示例使用以下

9、工具导入 Excel 数据： SQL Server 数据传输服务 (DTS) Microsoft SQL Server 2005 Integration Services (SSIS) SQL Server 链接服务器 SQL Server 分布式查询 ActiveX 数据对象 (ADO) 和 Microsoft OLE DB Provider for SQL Server ADO 和 Microsoft OLE DB Provider for Jet 4.0回到顶端要求下面的列表列出了推荐使用的硬件、软件、网络架构以及所需的 Service Pack： Microsoft SQL Serve

10、r 7.0、Microsoft SQL Server 2000 或 Microsoft SQL Server 2005 的可用实例 Microsoft Visual Basic 6.0（针对使用 Visual Basic 的 ADO 示例）本文的部分内容假定您熟悉下列主题： 数据传输服务 链接服务器和分布式查询 Visual Basic 中的 ADO 开发回到顶端示例导入与追加本文使用的示例 SQL 语句演示了“创建表”查询。该查询通过使用 SELECT.INTO.FROM 语法将 Excel 数据导入新的 SQL Server 表。如这些代码示例所示，在继续引用源对象和目标对象时，可以通过使

11、用 INSERT INTO.SELECT.FROM 语法将这些语句转换成追加查询。使用 DTS 或 SSIS可以使用“SQL Server Data Transformation Services (DTS) 导入向导”或“SQL Server 导入和导出向导”将 Excel 数据导入到 SQL Server 表中。在逐步执行向导并选择 Excel 源表时，要记住附加美元符号 ($) 的 Excel 对象名称代表工作表（例如，Sheet1$），而没有美元符号的普通对象名称代表 Excel 指定的范围。使用链接服务器要简化查询，可以将 Excel 工作簿配置为 SQL Server 中的链接服务

12、器。 有关其他信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 306397 ( ) 如何：结合 SQL Server 链接的服务器和分布式查询使用 Excel 下列代码将 Excel 链接服务器“EXCELLINK”上的 Customers 工作表数据导入新的名为 XLImport1 的 SQL Server 表： SELECT * INTO XLImport1 FROM EXCELLINK.Customers$ 还可以通过按照以下方式使用 OPENQUERY 以全通过方式对源数据执行查询： SELECT * INTO XLImport2 FROM OPENQUE

13、RY(EXCELLINK, SELECT * FROM Customers$) 使用分布式查询如果不想将对 Excel 工作簿的永久连接配置为链接服务器，可以通过使用 OPENDATASOURCE 或 OPENROWSET 函数为特定目的导入数据。下列代码示例也能将 Excel Customers 工作表数据导入新的 SQL Server 表： SELECT * INTO XLImport3 FROM OPENDATASOURCE(Microsoft.Jet.OLEDB.4.0,Data Source=C:testxltest.xls;Extended Properties=Excel 8.0

14、).Customers$SELECT * INTO XLImport4 FROM OPENROWSET(Microsoft.Jet.OLEDB.4.0,Excel 8.0;Database=C:testxltest.xls, Customers$)SELECT * INTO XLImport5 FROM OPENROWSET(Microsoft.Jet.OLEDB.4.0,Excel 8.0;Database=C:testxltest.xls, SELECT * FROM Customers$) 使用 ADO 和 SQLOLEDB当通过使用 Microsoft OLE DB for SQL S

15、erver (SQLOLEDB) 在 ADO 应用程序中连接到 SQL Server 时，可以使用与“使用分布式查询” 一节中相同的“分布式查询”语法将 Excel 数据导入 SQL Server。下列 Visual Basic 6.0 代码示例要求添加对 ActiveX 数据对象 (ADO) 的项目引用。此代码示例还演示了如何在 SQLOLEDB 连接上使用 OPENDATASOURCE 和 OPENROWSET。 Dim cn As ADODB.Connection Dim strSQL As String Dim lngRecsAff As Long Set cn = New ADODB

16、.Connection cn.Open Provider=SQLOLEDB;Data Source=; & _ Initial Catalog=;User ID=;Password= Import by using OPENDATASOURCE. strSQL = SELECT * INTO XLImport6 FROM & _ OPENDATASOURCE(Microsoft.Jet.OLEDB.4.0, & _ Data Source=C:testxltest.xls; & _ Extended Properties=Excel 8.0).Customers$ Debug.Print st

17、rSQL cn.Execute strSQL, lngRecsAff, adExecuteNoRecords Debug.Print Records affected: & lngRecsAff Import by using OPENROWSET and object name. strSQL = SELECT * INTO XLImport7 FROM & _ OPENROWSET(Microsoft.Jet.OLEDB.4.0, & _ Excel 8.0;Database=C:testxltest.xls, & _ Customers$) Debug.Print strSQL cn.E

18、xecute strSQL, lngRecsAff, adExecuteNoRecords Debug.Print Records affected: & lngRecsAff Import by using OPENROWSET and SELECT query. strSQL = SELECT * INTO XLImport8 FROM & _ OPENROWSET(Microsoft.Jet.OLEDB.4.0, & _ Excel 8.0;Database=C:testxltest.xls, & _ SELECT * FROM Customers$) Debug.Print strSQ

19、L cn.Execute strSQL, lngRecsAff, adExecuteNoRecords Debug.Print Records affected: & lngRecsAff cn.Close Set cn = Nothing 使用 ADO 和 Jet Provider上一节中的示例使用 ADO 和 SQLOLEDB Provider 连接到从 Excel 到 SQL 导入的目标。也可以使用 OLE DB Provider for Jet 4.0 来连接到 Excel 源。Jet 数据引擎可以通过使用具有三种不同格式的特殊语法来在 SQL 语句中引用外部数据库： Full pat

20、h to Microsoft Access database.Table Name ISAM Name;ISAM Connection String.Table Name ODBC;ODBC Connection String.Table Name本节使用第三种格式创建到目标 SQL Server 数据库的 ODBC 连接。可以使用 ODBC 数据源名称 (DSN) 或者 DSN-less 连接字符串： DSN: odbc;DSN=;UID=;PWD=DSN-less: odbc;Driver=SQL Server;Server=;Database=; UID=;PWD= 下列 Visual

21、Basic 6.0 代码示例要求添加对 ADO 的项目引用。此代码示例演示了如何使用 Jet 4.0 Provider 通过 ADO 连接将 Excel 数据导入到 SQL Server。 Dim cn As ADODB.Connection Dim strSQL As String Dim lngRecsAff As Long Set cn = New ADODB.Connection cn.Open Provider=Microsoft.Jet.OLEDB.4.0; & _ Data Source=C:testxltestt.xls; & _ Extended Properties=Exc

22、el 8.0 Import by using Jet Provider. strSQL = SELECT * INTO odbc;Driver=SQL Server; & _ Server=;Database=; & _ UID=;PWD=.XLImport9 & _ FROM Customers$ Debug.Print strSQL cn.Execute strSQL, lngRecsAff, adExecuteNoRecords Debug.Print Records affected: & lngRecsAff cn.Close Set cn = Nothing 也可以通过使用该语法（

23、Jet Provider 支持）将 Excel 数据导入其他 Microsoft Access 数据库、索引顺序存取方法 (ISAM)（“desktop”）数据库或 ODBC 数据库。回到顶端疑难解答 记住附加美元符号 ($) 的 Excel 对象名称代表工作表（例如：Sheet1$），而普通对象名称代表 Excel 指定的范围。 在某些环境中，特别是用表名称取代 SELECT 查询指派 EXCEL 源数据时，目标 SQL Server 表中的列会按照字母顺序重排。有关 Jet Provider 中存在的这一问题的其他信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章：

24、 299484 ( ) PRB：使用 ADOX 检索 Access 表的列时，列按字母顺序排列 当 Jet Provider 确定一个 Excel 列包含了混合文本和数值数据时，Jet Provider 会选择“majority”数据类型并将不匹配的值以 NULL 形式返回。有关如何解决这个问题的其他信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 194124 ( ) PRB：使用 DAO OpenRecordset 时 Excel 返回值为 NULL 回到顶端参考 有关如何将 Excel 用作数据源的其他信息，请单击下面的文章编号，以查看 Microsoft 知

25、识库中相应的文章： 257819 (http:/suppo.有关如何将 Excel 用作数据源的其他信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 257819 ( ) 如何：在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据 有关如何将数据传输到 Excel 中的其他信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 295646 ( ) 如何：使用 ADO 将数据从 ADO 数据源传输到 Excel 247412 ( ) INFO：将数据从 Visual Basic 传输到 Excel 的方法 246335 ( ) 如何：使用“自动化”功能将数据从 ADO 记录集传输到 Excel 319951 ( ) 如何：通过 SQL Server 数据传输服务向 Excel 传送数据 306125 ( ) 如何：将数据从 Microsoft SQL Server 导入 Microsoft Excel