熟悉SQL SERVER 的数据库管理员都知道其DTS可以进行数据的导入导出其实我们也可以使用TransactSQL语句进行导入导出操作在TransactSQL语句中我们主要使用OpenDataSource函数OPENROWSET 函数关于函数的详细说明请参考SQL联机帮助利用下述方法可以十分容易地实现SQL SERVERACCESSEXCEL数据转换详细说明如下
一 SQL SERVER 和ACCESS的数据导入导出
常规的数据导入导出
使用DTS向导迁移你的Access数据到SQL Server你可以使用这些步骤:
在SQL SERVER企业管理器中的Tools（工具）菜单上选择Data Transformation
Services（数据转换服务）然后选择 czdImport Data（导入数据）
在Choose a Data Source（选择数据源）对话框中选择Microsoft Access as the Source然后键入你的mdb数据库(mdb文件扩展名)的文件名或通过浏览寻找该文件
在Choose a Destination（选择目标）对话框中选择Microsoft OLEDB Prov ider for SQLServer选择数据库服务器然后单击必要的验证方式
在Specify Table Copy（指定表格复制）或Query（查询）对话框中单击Copy tables（复制表格）
在Select Source Tables（选择源表格）对话框中单击Select All（全部选定）下一步完成
TransactSQL语句进行导入导出
在SQL SERVER里查询access数据:
SELECT *
FROM OpenDataSource( MicrosoftJetOLEDB
Data Source=c:\DBmdb;User ID=Admin;Password=)表名
将access导入SQL server
在SQL SERVER 里运行:
SELECT *
INTO newtable
FROM OPENDATASOURCE (MicrosoftJetOLEDB
Data Source=c:\DBmdb;User ID=Admin;Password= )表名
将SQL SERVER表里的数据插入到Access表中
在SQL SERVER 里运行
insert into OpenDataSource( MicrosoftJetOLEDB
Data Source= c:\DBmdb;User ID=Admin;Password=)表名
(列名列名)
select 列名列名 from sql表
实例
insert into OPENROWSET(MicrosoftJetOLEDB
C:\dbmdb;admin; Test)
select idname from Test
INSERT INTO OPENROWSET(MicrosoftJetOLEDB c:\trademdb; admin; 表名)
SELECT *
FROM sqltablename
二 SQL SERVER 和EXCEL的数据导入导出
在SQL SERVER里查询Excel数据:
SELECT *
FROM OpenDataSource( MicrosoftJetOLEDB
Data Source=c:\bookxls;User ID=Admin;Password=;Extended properties=Excel )[Sheet$]
下面是个查询的示例它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格
SELECT *
FROM OpenDataSource ( MicrosoftJetOLEDB
Data Source=c:\Finance\accountxls;User ID=Admin;Password=;Extended properties=Excel )xactions
将Excel的数据导入SQL server :
SELECT * into newtable
FROM OpenDataSource( MicrosoftJetOLEDB
Data Source=c:\bookxls;User ID=Admin;Password=;Extended properties=Excel )[Sheet$]
实例:
SELECT * into newtable
FROM OpenDataSource( MicrosoftJetOLEDB
Data Source=c:\Finance\accountxls;User ID=Admin;Password=;Extended properties=Excel )xactions
将SQL SERVER中查询到的数据导成一个Excel文件
TSQL代码
EXEC masterxp_cmdshell bcp 库名dbo表名out c:\Tempxls c q Sservername Usa P
参数S 是SQL服务器名U是用户P是密码
说明还可以导出文本文件等多种格式
实例:EXEC masterxp_cmdshell bcp saletesttmpdboCusAccount out c:\tempxls c q Spmserver Usa Psa
EXEC masterxp_cmdshell bcp SELECT au_fname au_lname FROM pubsauthors ORDER BY au_lname queryout C:\ authorsxls c Sservername Usa Ppassword
在VB中应用ADO导出EXCEL文件代码
Dim cn As New ADODBConnection
cnopen Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=;
cnexecute masterxp_cmdshell bcp SELECT col col FROM 库名dbo表名 queryout E:\DTxls c Sservername Usa Ppassword
在SQL SERVER里往Excel插入数据:
insert into OpenDataSource( MicrosoftJetOLEDB
Data Source=c:\Tempxls;User ID=Admin;Password=;Extended properties=Excel )table (AAA) values ()
TSQL代码
INSERT INTO
OPENDATASOURCE(MicrosoftJETOLEDB
Extended Properties=Excel ;Data source=C:\training\inventurxls)[Filiale$]
(bestand produkt) VALUES ( Test)
