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

dianlanren

我们有时候需要在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 [Sheet1$]') where c like '%f%'

select * from

OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\book1.xls',[sheet1$])

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 [Sheet1$]')

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 [Sheet2$]')(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 [Sheet1$]')

　　如果表table1_bak不存在，则创建

　　有关BCP和分布式查询，您可以查SQLServer自带帮助。掌握了以上原理，你再进行SQLServer和txt文件、HTML文件、VFP文件的数据传输和交换都会很容易理解了。