SQL codeSQL SERVER 与ACCESS EXCEL的数据转换

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

2、据导入导出:使用 DTS 向导迁移你的 Access 数据到 SQL Server,你可以使用这些步骤 :1在 SQL SERVER企业管理器中的 T ools (工具菜单上,选择 Data Transformation 2Services (数据转换服务 ,然后选择 czdImport Data(导入数据 。3在 Choose a Data Source(选择数据源对话框中选择 Microsoft Access as the Source, 然后键入你的 .mdb 数据库 (.mdb文件扩展名 的文件名或通过浏览寻找该文件。4在 Choose a Destination(选择目标 对话框中,

3、 选择 Microsoft OLEDB 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.O

4、LEDB.4.0','Data Source="c:DB.mdb"User ID=Admin;Password='.表名-2. 将 access 导入 SQL server- =在 SQL SERVER 里运行 :SELECT *INTO newtableFROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="c:DB.mdb"User ID=Admin;Password=' .表名-3. 将 SQL SERVER表里的数据插入到 A

5、ccess 表中- =在 SQL SERVER 里运行:insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=" c:DB.mdb"User ID=Admin;Password='.表名(列名 1, 列名 2select 列名 1, 列名 2 from sql表实例:insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:db.mdb''admin'''

6、, Testselect id,name from TestINSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:trade.mdb' 'admin' '', 表名 SELECT *FROM sqltablename-二、 SQL SERVER 和 EXCEL 的数据导入导出1、在 SQL SERVER里查询 Excel 数据 :- =SELECT *FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data

7、Source="c:book1.xls"User ID=Admin;Password=;Extended properties=Excel 5.0'.Sheet1$下面是个查询的示例,它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。 SELECT *FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:Financeaccount.xls"User ID=Admin;Password=;Extended properties

8、=Excel 5.0'.xactions-2、将 Excel 的数据导入 SQL server :- =SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:book1.xls"User ID=Admin;Password=;Extended properties=Excel 5.0'.Sheet1$实例 :SELECT * into newtableFROM OpenDataSource( 'Microsoft

9、.Jet.OLEDB.4.0','Data Source="c:Financeaccount.xls"User ID=Admin;Password=;Extended properties=Excel 5.0'.xactions-3、将 SQL SERVER中查询到的数据导成一个 Excel 文件- =T-SQL 代码:EXEC master.xp_cmdshell 'bcp 库名 .dbo. 表名 out c:Temp.xls -c -q -S"servername"-U"sa" -P"&q

10、uot;'参数:S 是 SQL 服务器名; U 是用户; P 是密码说明:还可以导出文本文件等多种格式实例 :EXEC master.xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'EXEC master.xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs.authors ORDER BY au_lna

11、me" queryout C: authors.xls -c -Sservername -Usa -Ppassword' 在 VB6中应用 ADO 导出 EXCEL 文件代码:cn.open "Driver=SQL Server;Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;" cn.execute "master.xp_cmdshell 'bcp "SELECT col1, col2 FROM 库 名 .dbo. 表 名 " queryout E:DT.xls -c -Sse

12、rvername -Usa -Ppassword'"-4、在 SQL SERVER里往 Excel 插入数据 :- =insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:Temp.xls"User ID=Admin;Password=;Extended properties=Excel 5.0'.table1 (A1,A2,A3 values (1,2,3T-SQL 代码:INSERT INTOOPENDATASOURCE('Mi

13、crosoft.JET.OLEDB.4.0','Extended Properties=Excel 8.0;Data source=C:traininginventur.xls'.Filiale1$ (bestand, produkt VALUES (20, 'Test'-总结:利用以上语句,我们可以方便地将 SQL SERVER、 ACCESS 和 EXCEL 电子表格软 件中的数据进行转换,为我们提供了极大方便!SQL2000导入 /导出 EXCEL1。从 Excel 文件中 , 导入数据到 SQL 数据库中 , 很简单 , 直接用下面的语句 :/*

14、=*/ -如果接受数据导入的表已经存在insert into 表 select * fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$-如果导入数据并生成表select * into 表 fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$2。 如果从 SQL 数据库中 , 导出数据到

15、 Excel, 如果 Excel 文件已经存在 , 而且已经按照要接收的 数据创建好表头 ,-简单的用:insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$select * from 表-如果 Excel 文件不存在 , 也可以用 BCP 来导成类 Excel 的文件 , 注意大小写 :-导出表的情况EXEC master.xp_cmdshell 'bcp 数据库名 .dbo. 表名 out "c:test

16、.xls" /c -/S"服务器名 " /U"用户 名 " -P"密码 "'-导出查询的情况EXEC master.xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs.authors ORDER BY au_lname" queryout "c:test.xls" /c -/S"服务器名 " /U"用户名 " -P"密码 "'-说明 :c:test

17、.xls 为导入 /导出的 Excel 文件名 .sheet1$ 为 Excel 文件的工作表名 , 一般要加上 $才能正常使用 .-*/3。下面是导出真正 Excel 文件的方法 :if exists (select * from dbo.sysobjects where id = object_id(N'dbo.p_exporttb' and OBJECTPROPERTY(id, N'IsProcedure' = 1drop procedure dbo.p_exporttbGOp_exporttb tbname='地区资料 ',path=&#

18、39;c:',fname='aa.xls'-*/create proc p_exporttbtbname sysname, -要导出的表名path nvarchar(1000, -文件存放目录fname nvarchar(250='' -文件名 , 默认为表名asdeclare err int,src nvarchar(255,desc nvarchar(255,out intdeclare obj int,constr nvarchar(1000,sql varchar(8000,fdlist varchar(8000-参数检测if isnull(fn

19、ame,''='' set fname=tbname+'.xls'-检查文件是否已经存在if right(path,1<>'' set path=path+''create table #tb(a bit,b bit,c bitset sql=path+fnameinsert into #tb exec master.xp_fileexist sql-数据库创建语句set sql=path+fnameif exists(select 1 from #tb where a=1set constr='

20、;DRIVER=Microsoft Excel Driver (*.xls;DSN=''''READONLY=FALSE' +'CREATE_DB="'+sql+'"DBQ='+sqlelseset constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES' +'DATABASE='+sql+'"'-连接数据库exec err=sp_o

21、acreate 'adodb.connection',obj outif err<>0 goto lberrexec err=sp_oamethod obj,'open',null,constrif err<>0 goto lberr/*-如果覆盖已经存在的表 , 就加上下面的语句-创建之前先删除表 /如果存在的话select sql='drop table '+tbname+''exec err=sp_oamethod obj,'execute',out out,sql-*/-创建表的 S

22、QLselect sql='',fdlist=''select fdlist=fdlist+','++'',sql=sql+','++' '+casewhen like '%char'then case when a.length>255 then 'memo'else 'text('+cast(a.length as varchar+'' endwhen like '%

23、int' or ='bit' then 'int'when like '%datetime' then 'datetime'when like '%money' then 'money'when like '%text' then 'memo'else endwhere not in('image','uniqueidentifier','

24、sql_variant','varbinary','binary','timestamp'and object_id(tbname=idselect sql='create table '+tbname+'('+substring(sql,2,8000+'',fdlist=substring(fdlist,2,8000exec err=sp_oamethod obj,'execute',out out,sqlif err<>0 goto lberrexec err

25、=sp_oadestroy obj-导入数据set sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;IMEX=1;DA TABASE='+path+fname+''','+tbname+'$'exec('insert into '+sql+'('+fdlist+' select '+fdlist+' from '+tbnamereturnlb

26、err:exec sp_oageterrorinfo 0,src out,desc outlbexit:select cast(err as varbinary(4 as 错误号,src as 错误源 ,desc as 错误描述select sql,constr,fdlistgoif exists (select * from dbo.sysobjects where id = object_id(N'dbo.p_exporttb' and OBJECTPROPERTY(id, N'IsProcedure' = 1drop procedure dbo.p_exp

27、orttbGOp_exporttb sqlstr='select * from 地区资料 ',path='c:',fname='aa.xls',sheetname='地区资料 '-*/create proc p_exporttbsqlstr varchar(8000, -查询语句 , 如果查询语句中使用了 order by ,请加上 top 100 percent path nvarchar(1000, -文件存放目录fname nvarchar(250, -文件名sheetname varchar(250=''

28、-要创建的工作表名 , 默认为文件名asdeclare err int,src nvarchar(255,desc nvarchar(255,out intdeclare obj int,constr nvarchar(1000,sql varchar(8000,fdlist varchar(8000-参数检测if isnull(fname,''='' set fname='temp.xls'if isnull(sheetname,''='' set sheetname=replace(fname,'.&#

29、39;,'#'-检查文件是否已经存在if right(path,1<>'' set path=path+''create table #tb(a bit,b bit,c bitset sql=path+fnameinsert into #tb exec master.xp_fileexist sql-数据库创建语句set sql=path+fnameif exists(select 1 from #tb where a=1set constr='DRIVER=Microsoft Excel Driver (*.xls;DSN=

30、''''READONLY=FALSE'+'CREATE_DB="'+sql+'"DBQ='+sqlelseset constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES' +'DATABASE='+sql+'"'-连接数据库exec err=sp_oacreate 'adodb.connection',obj outi

31、f err<>0 goto lberrexec err=sp_oamethod obj,'open',null,constrif err<>0 goto lberr-创建表的 SQLdeclare tbname sysnameset tbname='#tmp_'+convert(varchar(38,newid(set sql='select * into '+tbname+' from('+sqlstr+' a'exec(sqlselect sql='',fdlist=&#

32、39;'select fdlist=fdlist+','++'',sql=sql+','++' '+casewhen like '%char'then case when a.length>255 then 'memo'else 'text('+cast(a.length as varchar+'' endwhen like '%int' or ='bit'

33、then 'int'when like '%datetime' then 'datetime'when like '%money' then 'money'when like '%text' then 'memo'else endFROM tempdb.syscolumns a left join tempdb.systypes b on a.xtype=b.xusertype where not in('

34、image','uniqueidentifier','sql_variant','varbinary','binary','timestamp' and a.id=(select id from tempdb.sysobjects where name=tbnameif rowcount=0 returnselect sql='create table '+sheetname+'('+substring(sql,2,8000+'',fdlist=substri

35、ng(fdlist,2,8000exec err=sp_oamethod obj,'execute',out out,sqlif err<>0 goto lberrexec err=sp_oadestroy obj-导入数据set sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;DA TABASE='+path+fname+''','+sheetname+'$'exec(

36、9;insert into '+sql+'('+fdlist+' select '+fdlist+' from '+tbname+''set sql='drop table '+tbname+''exec(sqlreturnlberr:exec sp_oageterrorinfo 0,src out,desc outlbexit:select cast(err as varbinary(4 as 错误号,src as 错误源 ,desc as 错误描述select sql,constr,fd

37、list汇总的 OPENROWSET , OpenDataSource2007-09-21 11:54SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:account.xls', ' SELECT * FROM sheet1$'- 从 Excel 取数据SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:account.xls'

38、,Sheet1$SELECT a.*FROM OPENROWSET('SQLOLEDB','tao''sa''','SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname' AS aSELECT *FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:account.xls"User ID=Admin;Password=;Extended

39、properties=Excel 8.0'.Sheet1$-ODBC 的 OLE DB 提供程序SELECT a.* FROM table1 a LEFT JOIN OPENROWSET('MSDASQL','DRIVER=SQL Server;SERVER=,2412;UID=sa;PWD=',DbName.dbo.table2 AS b ON a.id = b.id ORDER BY a.ID DESC-将地址为 端口为 2412的 SQL SERVER 上的 table2表和本地服务器上的 table1表联接。SELECT * FROM (SELECT a.* FROM table1 a UNION SELECT b.* FROM OPE