数据导入导出

3楼这是在CSDN上邹键的东西，推荐给大家。

从Excel文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:/*===================================================================*/--如果接受数据导入的表已经存在insertinto表select*fromOPENROWSET(’MICROSOFT.JET.OLEDB.4.0’,’Excel5.0;HDR=YES;DATABASE=c:\\test.xls’,sheet1$)--如果导入数据并生成表select*into表fromOPENROWSET(’MICROSOFT.JET.OLEDB.4.0’,’Excel5.0;HDR=YES;DATABASE=c:\\test.xls’,sheet1$)/*===================================================================*/--如果从SQL数据库中,导出数据到Excel,如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的用:insertintoOPENROWSET(’MICROSOFT.JET.OLEDB.4.0’,’Excel5.0;HDR=YES;DATABASE=c:\\test.xls’,sheet1$)select*from表--如果Excel文件不存在,也可以用BCP来导成类Excel的文件,注意大小写:--导出表的情况EXECmaster..xp_cmdshell’bcp数据库名.dbo.表名out"c:\\test.xls"/c-/S"服务器名"/ U"用户名"-P"密码"’--导出查询的情况EXECmaster..xp_cmdshell’bcp"SELECTau_fname,au_lnameFROMpubs..authorsORDERBYau_ lname"queryout"c:\\test.xls"/c-/S"服务器名"/U"用户名"-P"密码"’说明.c:\\test.xls为导入/导出的Excel文件名.sheet1$为Excel文件的工作表名,一般要加上$才能正常使用.下面是导出真正Excel文件的方法:/*--数据导出EXCEL导出表中的数据到Excel,包含字段名,文件为真正的Excel文件,如果文件不存在,将自动创建文件,如果表不存在,将自动创建表基于通用性考虑,仅支持导出标准数据类型---*//*--调用示例p_exporttb@tbname=’地区资料’,@path=’c:\\’,@fname=’aa.xls’--*/ifexists(select*fromdbo.sysobjectswhereid=object_id(N’[dbo].[p_exporttb]’)and OBJECTPROPERTY(id,N’IsProcedure’)=1)dropprocedure[dbo].[p_exporttb]GOcreateprocp_exporttb@tbnamesysname,--要导出的表名,注意只能是表名/视图名@pathnvarchar(1000),--文件存放目录@fnamenvarchar(250)=’’--文件名,默认为表名asdeclare@errint,@srcnvarchar(255),@descnvarchar(255),@outintdeclare@objint,@constrnvarchar(1000),@sqlvarchar(8000),@fdlistvarchar(8000)--参数检测ifisnull(@fname,’’)=’’set@fname=@tbname+’.xls’--检查文件是否已经存在ifright(@path,1)<>’\\’set@path=@path+’\\’createtable#tb(abit,bbit,cbit)set@sql=@path+@fnameinsertinto#tbexecmaster..xp_fileexist@sql--数据库创建语句set@sql=@path+@fnameifexists(select1from#tbwherea=1)set@constr=’DRIVER={MicrosoftExcelDriver(*.xls)};DSN=’’’’;READONLY=FALSE’+’;CREATE_DB="’+@sql+’";DBQ=’+@sqlelseset@constr=’Provider=Microsoft.Jet.OLEDB.4.0;ExtendedProperties="Excel5.0;HDR= YES’+’;DATABASE=’+@sql+’"’--连接数据库exec@err=sp_oacreate’adodb.connection’,@objoutif@err<>0gotolberrexec@err=sp_oamethod@obj,’open’,null,@constrif@err<>0gotolberr--创建表的SQLselect@sql=’’,@fdlist=’’select@fdlist=@fdlist+’,’+,@sql=@sql+’,[’++’]’+in(’char’,’nchar’,’varchar’,’nvarchar’)then’text(’+cast(casewhena.length>255then255elsea.lengthendasvarchar)+’)’in(’tynyint’,’int’,’bigint’,’tinyint’)then’int’in(’smalldatetime’,’datetime’)then’datetime’in(’money’,’smallmoney’)then’money’endFROMsyscolumnsaleftjoinsystypesbona.xtype=b.xusertypenotin(’image’,’text’,’uniqueidentifier’,’sql_variant’,’ntext’,’varbinary’,’binary’,’timestamp’)andobject_id(@tbname)=idselect@sql=’createtable[’+@tbname+’](’+substring(@sql,2,8000)+’)’,@fdlist=substring(@fdlist,2,8000)exec@err=sp_oamethod@obj,’execute’,@outout,@sqlif@err<>0gotolberrexec@err=sp_oadestroy@obj--导入数据set@sql=’openrowset(’’MICROSOFT.JET.OLEDB.4.0’’,’’Excel5.0;HDR=YES;DATABASE=’+@path+@fname+’’’,[’+@tbname+’$])’exec(’insertinto’+@sql+’(’+@fdlist+’)select’+@fdlist+’from’+@tbname)returnlberr:execsp_oageterrorinfo0,@srcout,@descoutlbexit:selectcast(@errasvarbinary(4))as错误号,@srcas错误源,@descas错误描述select@sql,@constr,@fdlistgo*--数据导出EXCEL导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件,如果文件不存在,将自动创建文件,如果表不存在,将自动创建表基于通用性考虑,仅支持导出标准数据类型--*//*--调用示例p_exporttb@sqlstr=’select*from地区资料’,@path=’c:\\’,@fname=’aa.xls’,@sheetname=’地区资料’--*/ifexists(select*fromdbo.sysobjectswhereid=object_id(N’[dbo].[p_exporttb]’)andOBJECTPROPERTY(id,N’IsProcedure’)=1)dropprocedure[dbo].[p_exporttb]GOcreateprocp_exporttb@sqlstrsysname,--查询语句,如果查询语句中使用了orderby,请加上top100percent,注意,如果导出表/视图,用上面的存储过程@pathnvarchar(1000),--文件存放目录@fnamenvarchar(250),--文件名@sheetnamevarchar(250)=’’--要创建的工作表名,默认为文件名asdeclare@errint,@srcnvarchar(255),@descnvarchar(255),@outintdeclare@objint,@constrnvarchar(1000),@sqlvarchar(8000),@fdlistvarchar(8000)--参数检测ifisnull(@fname,’’)=’’set@fname=’temp.xls’ifisnull(@sheetname,’’)=’’set@sheetname=replace(@fname,’.’,’#’)--检查文件是否已经存在ifright(@path,1)<>’\\’set@path=@path+’\\’createtable#tb(abit,bbit,cbit)set@sql=@path+@fnameinsertinto#tbexecmaster..xp_fileexist@sql--数据库创建语句set@sql=@path+@fnameifexists(select1from#tbwherea=1)set@constr=’DRIVER={MicrosoftExcelDriver(*.xls)};DSN=’’’’;READONLY=FALSE’+’;CREATE_DB="’+@sql+’";DBQ=’+@sqlelseset@constr=’Provider=Microsoft.Jet.OLEDB.4.0;ExtendedProperties="Excel5.0;HDR=YES’+’;DATABASE=’+@sql+’"’--连接数据库exec@err=sp_oacreate’adodb.connection’,@objoutif@err<>0gotolberrexec@err=sp_oamethod@obj,’open’,null,@constrif@err<>0gotolberr--创建表的SQLdeclare@tbnamesysnameset@tbname=’##tmp_’+convert(varchar(38),newid())set@sql=’select*into[’+@tbname+’]from(’+@sqlstr+’)a’exec(@sql)select@sql=’’,@fdlist=’’select@fdlist=@fdlist+’,’+,@sql=@sql+’,[’++’]’+in(’char’,’nchar’,’varchar’,’nvarchar’)then’text(’+cast(casewhena.length>255then255elsea.lengthendasvarchar)+’)’in(’tynyint’,’int’,’bigint’,’tinyint’)then’int’in(’smalldatetime’,’datetime’)then’datetime’in(’money’,’smallmoney’)then’money’endFROMtempdb..syscolumnsaleftjointempdb..systypesbona.xtype=b.xusertypenotin(’image’,’text’,’uniqueidentifier’,’sql_variant’,’ntext’,’varbinary’,’binary’,’timestamp’)anda.id=(selectidfromtempdb..sysobjectswherename=@tbname)select@sql=’createtable[’+@sheetname+’](’+substring(@sql,2,8000)+’)’,@fdlist=substring(@fdlist,2,8000)exec@err=sp_oamethod@obj,’execute’,@outout,@sqlif@err<>0gotolberrexec@err=sp_oadestroy@obj--导入数据set@sql=’openrowset(’’MICROSOFT.JET.OLEDB.4.0’’,’’Excel5.0;HDR=YES;DATABASE=’+@path+@fname+’’’,[’+@sheetname+’$])’exec(’insertinto’+@sql+’(’+@fdlist+’)select’+@fdlist+’from[’+@tbname+’]’)set@sql=’droptable[’+@tbname+’]’exec(@sql)returnlberr:execsp_oageterrorinfo0,@srcout,@descoutlbexit:selectcast(@errasvarbinary(4))as错误号,@srcas错误源,@descas错误描述select@sql,@constr,@fdlistgo/*******导出到excelEXEC master..xp_cmdshell \'bcp SettleDB.dbo.shanghu out c:\\temp1.xls -c -q -S"GNETDATA/GNE TDATA" -U"sa" -P""\'/***********导入ExcelSELECT *FROM OpenDataSource( \'Microsoft.Jet.OLEDB.4.0\',\'Data Source="c:\\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0\')...xaction s/*动态文件名declare @fn varchar(20),@s varchar(1000)set @fn = \'c:\\test.xls\'set @s =\'\'\'Microsoft.Jet.OLEDB.4.0\'\',\'\'Data Source="\'+@fn+\'";User ID=Admin;Password=;Extended properties=Excel 5.0\'\'\'set @s = \'SELECT * FROM OpenDataSource (\'+@s+\')...sheet1$\'exec(@s)*/SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+\'\' 转换后的别名FROM OpenDataSource( \'Microsoft.Jet.OLEDB.4.0\',\'Data Source="c:\\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0\')...xaction s/********************** EXCEL导到远程SQLinsert OPENDATASOURCE(\'SQLOLEDB\',\'Data Source=远程ip;User ID=sa;Password=密码\').库名.dbo.表名 (列名1,列名2)SELECT 列名1,列名2FROM OpenDataSource( \'Microsoft.Jet.OLEDB.4.0\',\'Data Source="c:\\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0\')...xaction s/** 导入文本文件EXEC master..xp_cmdshell \'bcp dbname..tablename in c:\\DT.txt -c -Sservername -Usa -Ppasswor d\'/** 导出文本文件EXEC master..xp_cmdshell \'bcp dbname..tablename out c:\\DT.txt -c -Sservername -Usa -Ppassw ord\'或EXEC master..xp_cmdshell \'bcp "Select * from dbname..tablename" queryout c:\\DT.txt -c -Sserv ername -Usa -Ppassword\'导出到TXT文本，用逗号分开exec master..xp_cmdshell \'bcp "库名..表名" out "d:\\tt.txt" -c -t ,-U sa -P password\'BULK INSERT 库名..表名FROM \'c:\\test.txt\'WITH (FIELDTERMINATOR = \';\',ROWTERMINATOR = \'\\n\')--/* dBase IV文件select * fromOPENROWSET(\'MICROSOFT.JET.OLEDB.4.0\',\'dBase IV;HDR=NO;IMEX=2;DATABASE=C:\\\',\'select * from [客户资料4.dbf]\')--*/--/* dBase III文件select * fromOPENROWSET(\'MICROSOFT.JET.OLEDB.4.0\',\'dBase III;HDR=NO;IMEX=2;DATABASE=C:\\\',\'select * from [客户资料3.dbf]\')--*/--/* FoxPro 数据库select * from openrowset(\'MSDASQL\',\'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\\\',\'select * from [aa.DBF]\')--*//**************导入DBF文件****************/select * from openrowset(\'MSDASQL\',\'Driver=Microsoft Visual FoxPro Driver;SourceDB=e:\\VFP98\\data;SourceType=DBF\',\'select * from customer where country != "USA" order by country\')go/***************** 导出到DBF ***************/如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句insert into openrowset(\'MSDASQL\',\'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\\\',\'select * from [aa.DBF]\')select * from 表说明:SourceDB=c:\\指定foxpro表所在的文件夹aa.DBF指定foxpro表的文件名./*************导出到Access********************/insert into openrowset(\'Microsoft.Jet.OLEDB.4.0\',\'x:\\A.mdb\';\'admin\';\'\',A表) select * from 数据库名..B表/*************导入Access********************/insert into B表 selet * from openrowset(\'Microsoft.Jet.OLEDB.4.0\',\'x:\\A.mdb\';\'admin\';\'\',A表)文件名为参数declare @fname varchar(20)set @fname = \'d:\\test.mdb\'exec(\'SELECT a.* FROM opendatasource(\'\'Microsoft.Jet.OLEDB.4.0\'\',\'\'\'+@fname+\'\'\';\'\'admin\'\';\'\'\'\', topics) as a \')SELECT *FROM OpenDataSource( \'Microsoft.Jet.OLEDB.4.0\',\'Data Source="f:\\northwind.mdb";Jet OLEDB:Database Password=123;User ID=Admin;Password =;\')...产品*********************导入 xml文件DECLARE @idoc intDECLARE @doc varchar(1000)--sample XML documentSET @doc =\'Customer was very satisfiedImportantHappy Customer.\'-- Create an internal representation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @doc-- Execute a SELECT statement using OPENXML rowset provider.SELECT *FROM OPENXML (@idoc, \'/root/Customer/Order\', 1)WITH (oid char(5),amountfloat,comment ntext \'text()\')EXEC sp_xml_removedocument @idoc???????/**********************Excel导到Txt****************************************/想用select * into opendatasource(...) from opendatasource(...)实现将一个Excel文件内容导入到一个文本文件假设Excel中有两列，第一列为姓名，第二列为很行帐号(16位)且银行帐号导出到文本文件后分两部分，前8位和后8位分开。