dianlanren 发表于 2014-10-24 11:41:41

怎样用SQL语句在SQL Server和Excel及Access间交换数据

本帖最后由 dianlanren 于 2014-10-24 11:42 编辑

我们有时候需要在SQLServer和Access、Excel等数据存储文件间交换和传输数据,这其实在SQL Server中是一件非常简单的事情。
通常情况下,可以有3种方式:1、DTS工具 2、BCP 3、分布式查询
  DTS是图形化操作界面,很容易上手。这里主要讲下后面两种,分别以查询、插入、删除、更改作为简单的例子,下面直接以T-SQL的形式表现出来。
  一、SQLServer和Access
  1、在SQLServer中查询Access数据的方法:
select * from OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select
* from table1)

select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\DB2.mdb";User
ID=Admin; Password=')...table1
  2、从SQLServer向Access写数据:
insert into OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select
* from Accee_table')

select * from SQLServer_table

或用BCP
master..xp_cmdshell'bcp "serv-htjs.dbo.table1" out "c:\db3.mdb" -c
-q -S"." -U"sa" -P"sa"'
  上面的区别主要是:OpenRowSet需要mdb和表存在,BCP会在不存在的时候生成该mdb
  3、从Access向SQLServer写数据:有了上面的基础,这个就很简单了
insert into SQLServer_table1 select * from

OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from
Accee_table1')
或用BCP
master..xp_cmdshell'bcp "serv-htjs.dbo.table1" in "c:\db3.mdb" -c
-q -S"." -U"sa" -P"sa"'
  4、删除Access数据:
delete from OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select
* from tabel1')

where lock=0
  5、修改Access数据:
update OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select
* from table1')

set lock=1
SQLServer和Access大致就这么多。


  二、SQLServer和Excel

  1、向Excel查询
select * from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=c:\book1.xls;','select
* from ') where c like '%f%'
select * from

OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\book1.xls',)
1)hdr=yes时可以把xls的第1行作为字段看待,如第1个中hdr=no的话,where时就会报错

2)[]和美圆$必须要,否则M$可不认这个账
  2、修改Execl
update OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=c:\book1.xls;','select
* from ')

set a='erquan' where c like '%f%'
  3、导入导出
insert into OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=c:\book1.xls;','select
* from ')(id,name)

select id,name from table1
或BCP
master..xp_cmdshell'bcp "serv-htjs.dbo.table1" out "c:\book2.xls" -c
-q -S"." -U"sa" -P"sa"'
从Excel向SQLServer导入:
select * into table1_bak

from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=c:\book1.xls;','select
* from ')
  如果表table1_bak不存在,则创建
  有关BCP和分布式查询,您可以查SQLServer自带帮助。掌握了以上原理,你再进行SQLServer和txt文件、HTML文件、VFP文件的数据传输和交换都会很容易理解了。
页: [1]
查看完整版本: 怎样用SQL语句在SQL Server和Excel及Access间交换数据