使用TLSQL实现导出导出支持EXCLE的导入导出.docx
《使用TLSQL实现导出导出支持EXCLE的导入导出.docx》由会员分享，可在线阅读，更多相关《使用TLSQL实现导出导出支持EXCLE的导入导出.docx（11页珍藏版）》请在冰豆网上搜索。
使用TLSQL实现导出导出支持EXCLE的导入导出
使用T-SQL实现数据导出\导入（SQLSERVER—>SQLSERVER）
2007-07-1016:
12:
08
标签：
数据Transact-SQL
今天尝试使用Transact-SQL进行数据的导出导入，收获颇丰。
与使用DTS相比，效率要高很多！
一、打开OPENDATASOURCE功能
开始—>
所有程序 —>
MicrosoftSQLServer2005 —>
配置工具 —>
SQLServer外围应用配置器 —>
功能的外围应用配置器 —>
实例名 —>
DatabaseEngine —>
即席远程查询 —>
启用OpenRowset和OpenDatasource支持。
否则，会报错：
消息15281，级别16，状态1，第1行
SQLServer阻止了对组件'AdHocDistributedQueries'的STATEMENT'OpenRowset/OpenDatasource'的访问，因为此组件已作为此服务器
安全配置的一部分而被关闭。
系统管理员可以通过使用sp_configure启用'AdHocDistributedQueries'。
有关启用'AdHocDistributed
Queries'的详细信息，请参阅SQLServer联机丛书中的"外围应用配置器"。
二、打开远程连接
开始—>
所有程序 —>
MicrosoftSQLServer2005 —>
配置工具 —>
SQLServer外围应用配置器 —>
服务和连接的外围应用配置器 —>
实例名 —>
DatabaseEngine —>
远程连接 —>
本地连接和远程连接 —>
仅使用TCP/IP —>
应用 —>
重新启动数据库引擎
否则，会报错：
链接服务器"（null）"的OLEDB访问接口"SQLNCLI"返回了消息"登录超时已过期"。
链接服务器"（null）"的OLEDB访问接口"SQLNCLI"返回了消息"建立到服务器的连接时发生错误。
连接到SQLServer2005时，默认设置
SQLServer不允许远程连接这个事实可能会导致失败。
"。
消息65535，级别16，状态1，第0行
SQL网络接口:
从注册表获取已启用的协议列表时出错[xFFFFFFFF].
三、打开客户端TCP/IP协议
开始—>
所有程序 —>
MicrosoftSQLServer2005 —>
配置工具 —>
SQLServerConfigurationManager —>
SQLServer2005网络配置：
启用TCP/IP协议
SQLNativeClient 配置：
启用
默认端口-1433
四、创建联接服务器
execsp_addlinkedserver@server='conndb04',
@provider='sqloledb',
@srvproduct='',
@datasrc='WINNIE\IIDC'
execsp_addlinkedsrvlogin@rmtsrvname='WINNIE\IIDC',
@useself='false',
@locallogin='admin',
@rmtuser='admin',
@rmtpassword='password';
[注]这里的login帐户，应该是使用SQLServer身份验证的帐户，否则，会报错：
xx用户无法登陆。
。
。
五、在导入目标数据库中创建空表
usewwww
go
createtablepro_unit_sort（
CulIDvarchar（40）,
Coll_Unitvarchar（400）,
cul_Sortvarchar（400））
六、执行导出\导入
select*intowwww.dbo.pro_unit_sortfromconndb04.wwww_xx..dbo.xx._unit_sort;
[注]
1、第四步和第六步等同于：
usewwww
go
insertINTOOPENDATASOURCE（
'SQLOLEDB',
'DataSource=WINNIE\IIDC;
UserID=admin;
Password=password'
）.wwww.dbo.pro_unit_sort
select*fromwwww_xx.dbo.xx.unit_sort;
2、执行此操作时，将使用到UDP1434端口，如果防火墙关闭了此端口的话，请将其打开。
否则，会报错：
链接服务器"（null）"的OLEDB访问接口"SQLNCLI"返回了消息"登录超时已过期"。
链接服务器"（null）"的OLEDB访问接口"SQLNCLI"返回了消息"建立到服务器的连接时发生错误。
连接到SQLServer2005时，默认设置
SQLServer不允许远程连接这个事实可能会导致失败。
"。
消息65535，级别16，状态1，第0行
SQL网络接口:
从注册表获取已启用的协议列表时出错[xFFFFFFFF].
参考文档：
1、使用Transact-SQL进行数据导入导出方法详解：
2、SQLServer的链接服务器技术小结
如何将数据从Excel导入到SQLServer
查看本文应用于的产品
本页
∙
概要
o
技术说明
o
要求
o
示例
▪
导入与追加
▪
使用DTS或SSIS
▪
使用链接服务器
▪
使用分布式查询
▪
使用ADO和SQLOLEDB
▪
使用ADO和JetProvider
o
疑难解答
∙
参考
展开全部|关闭全部
概要
本文循序渐进地演示如何用不同的方法将数据从MicrosoftExcel工作表导入到MicrosoftSQLServer数据库。
技术说明本文中的示例...
本文循序渐进地演示如何用不同的方法将数据从MicrosoftExcel工作表导入到MicrosoftSQLServer数据库。
回到顶端
技术说明
本文中的示例使用以下工具导入Excel数据：
∙SQLServer数据传输服务（DTS）
∙MicrosoftSQLServer2005IntegrationServices（SSIS）
∙SQLServer链接服务器
∙SQLServer分布式查询
∙ActiveX数据对象（ADO）和MicrosoftOLEDBProviderforSQLServer
∙ADO和MicrosoftOLEDBProviderforJet4.0
回到顶端
要求
下面的列表列出了推荐使用的硬件、软件、网络架构以及所需的ServicePack：
∙MicrosoftSQLServer7.0、MicrosoftSQLServer2000或MicrosoftSQLServer2005的可用实例
∙MicrosoftVisualBasic6.0（针对使用VisualBasic的ADO示例）
本文的部分内容假定您熟悉下列主题：
∙数据传输服务
∙链接服务器和分布式查询
∙VisualBasic中的ADO开发
回到顶端
示例
导入与追加
本文使用的示例SQL语句演示了“创建表”查询。
该查询通过使用SELECT...INTO...FROM语法将Excel数据导入新的SQLServer表。
如这些代码示例所示，在继续引用源对象和目标对象时，可以通过使用INSERTINTO...SELECT...FROM语法将这些语句转换成追加查询。
使用DTS或SSIS
可以使用“SQLServerDataTransformationServices（DTS）导入向导”或“SQLServer导入和导出向导”将Excel数据导入到SQLServer表中。
在逐步执行向导并选择Excel源表时，要记住附加美元符号（$）的Excel对象名称代表工作表（例如，Sheet1$），而没有美元符号的普通对象名称代表Excel指定的范围。
使用链接服务器
要简化查询，可以将Excel工作簿配置为SQLServer中的链接服务器。
有关其他信息，请单击下面的文章编号，以查看Microsoft知识库中相应的文章：
306397 （）如何：
结合SQLServer链接的服务器和分布式查询使用Excel
下列代码将Excel链接服务器“EXCELLINK”上的Customers工作表数据导入新的名为XLImport1的SQLServer表：
SELECT*INTOXLImport1FROMEXCELLINK...[Customers$]
还可以通过按照以下方式使用OPENQUERY以全通过方式对源数据执行查询：
SELECT*INTOXLImport2FROMOPENQUERY（EXCELLINK,
'SELECT*FROM[Customers$]'）
使用分布式查询
如果不想将对Excel工作簿的永久连接配置为链接服务器，可以通过使用OPENDATASOURCE或OPENROWSET函数为特定目的导入数据。
下列代码示例也能将ExcelCustomers工作表数据导入新的SQLServer表：
SELECT*INTOXLImport3FROMOPENDATASOURCE（'Microsoft.Jet.OLEDB.4.0',
'DataSource=C:
\test\xltest.xls;ExtendedProperties=Excel8.0'）...[Customers$]
SELECT*INTOXLImport4FROMOPENROWSET（'Microsoft.Jet.OLEDB.4.0',
'Excel8.0;Database=C:
\test\xltest.xls',[Customers$]）
SELECT*INTOXLImport5FROMOPENROWSET（'Microsoft.Jet.OLEDB.4.0',
'Excel8.0;Database=C:
\test\xltest.xls','SELECT*FROM[Customers$]'）
使用ADO和SQLOLEDB
当通过使用MicrosoftOLEDBforSQLServer（SQLOLEDB）在ADO应用程序中连接到SQLServer时，可以使用与“使用分布式查询”一节中相同的“分布式查询”语法将Excel数据导入SQLServer。
下列VisualBasic6.0代码示例要求添加对ActiveX数据对象（ADO）的项目引用。
此代码示例还演示了如何在SQLOLEDB连接上使用OPENDATASOURCE和OPENROWSET。
DimcnAsADODB.Connection
DimstrSQLAsString
DimlngRecsAffAsLong
Setcn=NewADODB.Connection
cn.Open"Provider=SQLOLEDB;DataSource=;"&_
"InitialCatalog=;UserID=;Password="
'ImportbyusingOPENDATASOURCE.
strSQL="SELECT*INTOXLImport6FROM"&_
"OPENDATASOURCE（'Microsoft.Jet.OLEDB.4.0',"&_
"'DataSource=C:
\test\xltest.xls;"&_
"ExtendedProperties=Excel8.0'）...[Customers$]"
Debug.PrintstrSQL
cn.ExecutestrSQL,lngRecsAff,adExecuteNoRecords
Debug.Print"Recordsaffected:
"&lngRecsAff
'ImportbyusingOPENROWSETandobjectname.
strSQL="SELECT*INTOXLImport7FROM"&_
"OPENROWSET（'Microsoft.Jet.OLEDB.4.0',"&_
"'Excel8.0;Database=C:
\test\xltest.xls',"&_
"[Customers$]）"
Debug.PrintstrSQL
cn.ExecutestrSQL,lngRecsAff,adExecuteNoRecords
Debug.Print"Recordsaffected:
"&lngRecsAff
'ImportbyusingOPENROWSETandSELECTquery.
strSQL="SELECT*INTOXLImport8FROM"&_
"OPENROWSET（'Microsoft.Jet.OLEDB.4.0',"&_
"'Excel8.0;Database=C:
\test\xltest.xls',"&_
"'SELECT*FROM[Customers$]'）"
Debug.PrintstrSQL
cn.ExecutestrSQL,lngRecsAff,adExecuteNoRecords
Debug.Print"Recordsaffected:
"&lngRecsAff
cn.Close
Setcn=Nothing
使用ADO和JetProvider
上一节中的示例使用ADO和SQLOLEDBProvider连接到从Excel到SQL导入的目标。
也可以使用OLEDBProviderforJet4.0来连接到Excel源。
Jet数据引擎可以通过使用具有三种不同格式的特殊语法来在SQL语句中引用外部数据库：
∙[FullpathtoMicrosoftAccessdatabase].[TableName]
∙[ISAMName;ISAMConnectionString].[TableName]
∙[ODBC;ODBCConnectionString].[TableName]
本节使用第三种格式创建到目标SQLServer数据库的ODBC连接。
可以使用ODBC数据源名称（DSN）或者DSN-less连接字符串：
DSN:
[odbc;DSN=;UID=;PWD=]
DSN-less:
[odbc;Driver={SQLServer};Server=;Database=;
UID=;PWD=]
下列VisualBasic6.0代码示例要求添加对ADO的项目引用。
此代码示例演示了如何使用Jet4.0Provider通过ADO连接将Excel数据导入到SQLServer。
DimcnAsADODB.Connection
DimstrSQLAsString
DimlngRecsAffAsLong
Setcn=NewADODB.Connection
cn.Open"Provider=Microsoft.Jet.OLEDB.4.0;"&_
"DataSource=C:
\test\xltestt.xls;"&_
"ExtendedProperties=Excel8.0"
'ImportbyusingJetProvider.
strSQL="SELECT*INTO[odbc;Driver={SQLServer};"&_
"Server=;Database=;"&_
"UID=;PWD=].XLImport9"&_
"FROM[Customers$]"
Debug.PrintstrSQL
cn.ExecutestrSQL,lngRecsAff,adExecuteNoRecords
Debug.Print"Recordsaffected:
"&lngRecsAff
cn.Close
Setcn=Nothing
也可以通过使用该语法（JetProvider支持）将Excel数据导入其他MicrosoftAccess数据库、索引顺序存取方法（ISAM）（“desktop”）数据库或ODBC数据库。
回到顶端
疑难解答
∙记住附加美元符号（$）的Excel对象名称代表工作表（例如：
Sheet1$），而普通对象名称代表Excel指定的范围。
∙在某些环境中，特别是用表名称取代SELECT查询指派EXCEL源数据时，目标SQLServer表中的列会按照字母顺序重排。
有关JetProvider中存在的这一问题的其他信息，请单击下面的文章编号，以查看Microsoft知识库中相应的文章：
299484 （）PRB：
使用ADOX检索Access表的列时，列按字母顺序排列
∙当JetProvider确定一个Excel列包含了混合文本和数值数据时，JetProvider会选择“majority”数据类型并将不匹配的值以NULL形式返回。
有关如何解决这个问题的其他信息，请单击下面的文章编号，以查看Microsoft知识库中相应的文章：
194124 （）PRB：
使用DAOOpenRecordset时Excel返回值为NULL
回到顶端
参考
有关如何将Excel用作数据源的其他信息，请单击下面的文章编号，以查看Microsoft知识库中相应的文章：
257819 （http:
//suppo...
有关如何将Excel用作数据源的其他信息，请单击下面的文章编号，以查看Microsoft知识库中相应的文章：
257819 （）如何：
在VisualBasic或VBA中使用ADO来处理Excel数据
有关如何将数据传输到Excel中的其他信息，请单击下面的文章编号，以查看Microsoft知识库中相应的文章：
295646 （）如何：
使用ADO将数据从ADO数据源传输到Excel
247412 （）INFO：
将数据从VisualBasic传输到Excel的方法
246335 （）如何：
使用“自动化”功能将数据从ADO记录集传输到Excel
319951 （）如何：
通过SQLServer数据传输服务向Excel传送数据
306125 （）如何：
将数据从MicrosoftSQLServer导入MicrosoftExcel
升级会员 