SQL SERVER 与ACCESS、EXCEL的数据转换

熟悉SQL SERVER 2000的人员都知道，DTS可以进行数据的导入导出，但我们也可以使用Transact-SQL语句进行导入导出操作。在Transact-SQL语句中，我们主要使用OpenDataSource函数、OPENROWSET 函数，关于函数的详细说明，请参考SQL联机帮助。利用下述方法，可以十分容易地实现SQL SERVER、ACCESS、EXCEL数据转换,说明如下.

 

一. SQL SERVER 和ACCESS的数据导入导出

 

常规的数据导入导出：

使用DTS向导迁移你的Access数据到SQL Server，你可以使用这些步骤:
　

  1.在SQL SERVER企业管理器中的Tools（工具）菜单上，选择Data Transformation

　2.Services（数据转换服务），然后选择  czdImport Data（导入数据）。

　3.在Choose a Data Source（选择数据源）对话框中选择Microsoft Access as the Source，然后键入你的.mdb数据库(.mdb文件扩展名)的文件名或通过浏览寻找该文件。

　4.在Choose a Destination（选择目标）对话框中，选择Microsoft OLE　DB Prov ider for SQL　Server，选择数据库服务器，然后单击必要的验证方式。

　5.在Specify Table Copy（指定表格复制）或Query（查询）对话框中，单击Copy tables（复制表格）。

  6.在Select Source Tables（选择源表格）对话框中，单击Select All（全部选定）。下一步，完成。

---

  Transact-SQL语句进行导入导出：

  1.在sql server里查询access数据

  select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data  Source="c:/DB.mdb";User ID=Admin;Password=')...表名

 

  2. access-->sql server在查询分析器里运行如下语句

    select * into newtable from OpenDataSource ('Microsoft.Jet.OLEDB.4.0', 'Data Source="c:/DB.mdb";User ID=Admin;Password=' )...表名

 

  3. 插入access里

  insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=" c:/DB.mdb";User ID=Admin;Password=')...表名
(列名1,列名2) select 列名1,列名2  from  sql表

 

  4.查询execl数据与查询access数据类似

 

  5.导入execl数据

/*Declare
@Sql_OPENDATASOURCE varchar(8000),
@Sql_OPENROWSET varchar(8000),
@Sql_OPENROWSET_Select varchar(8000),
@ExeclPath varchar(1000),
@ExeclObject varchar(128)
set @ExeclPath='D:/Excels.xls'
set @ExeclObject='spzls'
set @sql_OPENDATASOURCE='SELECT *  FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'+''''+','+''''
+'Data Source='+rtrim(@ExeclPath)+';Extended Properties=Excel 8.0'+''''+')...['+rtrim(@ExeclObject)+'$]'
Exec (@Sql_OPENDATASOURCE)
select @Sql_OPENDATASOURCE
SELECT *  FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=D:/Excels.xls;Extended Properties=Excel 8.0')...[spzls$]
--
set @Sql_OPENROWSET='SELECT *  FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'+''''+','+''''
+'Excel 8.0;DataBase='+rtrim(@ExeclPath)+''''+',['+rtrim(@ExeclObject)+'$])'
Exec (@sql_OPENROWSET)
select @Sql_OPENROWSET
SELECT *  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;DataBase=D:/Excels.xls',[spzls$])
--
set @Sql_OPENROWSET_Select='SELECT *  FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'+''''+','+''''
+'Excel 8.0;DataBase='+rtrim(@ExeclPath)+''''+','+''''+'SELECT * FROM ['+rtrim(@ExeclObject)+'$]'+''''+')'
Exec (@Sql_OPENROWSET_Select)
select @Sql_OPENROWSET_Select
*/

 ====

SELECT * into tablename  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel8.0;DataBase=D:/Excels.xls','SELECT * FROM [spzls$]')

-----
 select * into tablename from OpenDataSource('microsoft.Jet.OleDb.4.0','Data Source='D:/excels.xls';user id='admin';password=;Extended properties='Excel 5.0')...[spzls$]

===

 

  5.sql server-->excel

      A.EXEC master..xp_cmdshell 'bcp dbname.dbo.tbname out d:/Temp.xls -c -q -S"servername" -U"sa" -P"dell1400"'

      B.EXEC master..xp_cmdshell 'bcp select column1,column2 dbname.dbo.tbname out d:/Temp.xls -c -q -S"servername" -U"sa" -P"dell1400"'

     

      C.insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="D:/Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1(A1,A2,A3) values (1,2,3)