SQL语句导入导出大全.doc

SQL语句导入导出大全收集正巧这两天要用到这个，上网找到一个，回来自己检测一下：/* 导出到excelEXEC master.xp_cmdshell bcp SettleDB.dbo.shanghu out c:temp1.xls -c -q -SGNETDATA/GNETDATA -Usa -P/* 导入ExcelSELECT * FROM OpenDataSource( Microsoft.Jet.OLEDB.4.0, Data Source=c:test.xls;User ID=Admin;Password=;Extended properties=Excel 5.0).xactions/*动态文件名declare fn varchar(20),s varchar(1000)set fn = c:test.xlsset s =Microsoft.Jet.OLEDB.4.0,Data Source=+fn+;User ID=Admin;Password=;Extended properties=Excel 5.0set 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).xactions/* 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).xactions/* 导入文本文件EXEC master.xp_cmdshell bcp dbname.tablename in c:DT.txt -c -Sservername -Usa -Ppassword/* 导出文本文件EXEC master.xp_cmdshell bcp dbname.tablename out c:DT.txt -c -Sservername -Usa -Ppassword或EXEC master.xp_cmdshell bcp Select * from dbname.tablename queryout c:DT.txt -c -Sservername -Usa -Ppassword导出到TXT文本，用逗号分开exec master.xp_cmdshell bcp 库名.表名 out d:tt.txt -c -t ,-U sa -P passwordBULK INSERT 库名.表名FROM c:test.txtWITH ( FIELDTERMINATOR = ;, ROWTERMINATOR = n)-/* dBase IV文件select * from OPENROWSET(MICROSOFT.JET.OLEDB.4.0,dBase IV;HDR=NO;IMEX=2;DATABASE=C:,select * from 客户资料4.dbf)-*/-/* dBase III文件select * from OPENROWSET(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:VFP98data;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.mdbexec(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 satisfied Important Happy 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), amount float, comment ntext text()EXEC sp_xml_removedocument idoc ?/*Excel导到Txt*/想用select * into opendatasource(.) from opendatasource(.)实现将一个Excel文件内容导入到一个文本文件假设Excel中有两列，第一列为姓名，第二列为很行帐号(16位)且银行帐号导出到文本文件后分两部分，前8位和后8位分开。邹健：如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2然后就可以用下面的语句进行插入注意文件名和目录根据你的实际情况进行修改.insert intoopendatasource(MICROSOFT.JET.OLEDB.4.0,Text;HDR=Yes;DATABASE=C:).aa#txt-,aa#txt)-*/select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8) from opendatasource(MICROSOFT.JET.OLEDB.4.0,Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:a.xls-,Sheet1$).Sheet1$ 如果你想直接插入并生成文本文件,就要用bcpdeclare sql varchar(8000),tbname varchar(50)-首先将excel表内容导入到一个全局临时表select tbname=#temp+cast(newid() as varchar(40)+ ,sql=select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8) into +tbname+ from opendatasource(MICROSOFT.JET.OLEDB.4.0,Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:a.xls).Sheet1$exec(sql)-然后用bcp从全局临时表导出到文本文件set sql=bcp +tbname+ out c:aa.txt /S(local) /P /cexec master.xp_cmdshell sql-删除临时表exec(drop table +tbname)/*导整个数据库*/用bcp实现的存储过程/* 实现数据导入/导出的存储过程 根据不同的参数,可以实现导入/导出整个数据库/单个表 调用示例:-导出调用示例-导出单个表exec file2table zj,xzkh_sa.地区资料,c:zj.txt,1-导出整个数据库exec file2table zj,xzkh_sa,C:docman,1-导入调用示例-导入单个表exec file2table zj,xzkh_sa.地区资料,c:zj.txt,0-导入整个数据库exec file2table zj,xzkh_sa,C:docman,0*/if exists(select 1 from sysobjects where name=File2Table and objectproperty(id,IsProcedure)=1) drop procedure File2Tablegocreate procedure File2Tableservername varchar(200) -服务器名,username varchar(200) -用户名,如果用NT验证方式,则为空,password varchar(200) -密码,tbname varchar(500) -数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表,filename varchar(1000) -导入/导出路径/文件名,如果tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt,isout bit -1为导出,0为导入asdeclare sql varchar(8000)if tbname like %.%.% -如果指定了表名,则直接导出单个表begin set sql=bcp +tbname +case when isout=1 then out else in end + +filename+ /w + /S +servername +case when isnull(username,)= then else /U +username end + /P +isnull(password,) exec master.xp_cmdshell sqlendelsebegin -导出整个数据库,定义游标,取出所有的用户表 declare m_tbname varchar(250) if right(filename,1) set filename=filename+ set m_tbname=declare #tb cursor for select name from +tbname+.sysobjects where xtype=U exec(m_tbname) open #tb fetch next from #tb into m_tbname while fetch_status=0 begin set sql=bcp +tbname+.+m_tbname +case when isout=1 then out else in end + +filename+m_tbname+.txt /w + /S +servername +case when isnull(username,)= then else /U +username end + /P +isnull(password,) exec master.xp_cmdshell sql fetch next from #tb into m_tbname end close #tb deallocate #tb endgo/* Oracle */EXEC sp_addlinkedserver OracleSvr, Oracle 7.3, MSDAORA, ORCLDBGOdelete from openquery(mailser,select * from yulin)select * from openquery(mailser,select * from yulin)update openquery(mailser,select * from yulin where id=15)set disorder=555,catago=888insert into openquery(mailser,select disorder,catago from yulin)values(333,777) 补充：对于用bcp导出,是没有字段名的.用openrowset导出,需要事先建好表.用openrowset导入,除ACCESS及EXCEL外,均不支持非本机数据导入 posted on 2005-08-02 23:03 任搏软 阅读(1133) 评论(4) 编辑 收藏 引用 网摘 所属分类: DataBase FeedBack: # re: SQL语句导入导出大全收集 2005-08-06 00:32 任搏软熟悉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 2Services（数据转换服务），然后选择 czdImport Data（导入数据）。 3在Choose a Data Source（选择数据源）对话框中选择Microsoft Access as the Source，然后键入你的.mdb数据库(.mdb文件扩展名)的文件名或通过浏览寻找该文件。 4在Choose a Destination（选择目标）对话框中，选择Microsoft OLEDB Prov ider for SQLServer，选择数据库服务器，然后单击必要的验证方式。 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 - = 在SQL SERVER 里运行: SELECT * INTO newtable FROM OPENDATASOURCE (Microsoft.Jet.OLEDB.4.0, Data Source=c:DB.mdb;User ID=Admin;Password= ).表名 3. 将SQL SERVER表里的数据插入到Access表中 - = 在SQL SERVER 里运行： insert into OpenDataSource( Microsoft.Jet.OLEDB.4.0, Data Source= c:DB.mdb;User ID=Admin;Password=).表名 (列名1,列名2) select 列名1,列名2 from sql表 实例： insert into OPENROWSET(Microsoft.Jet.OLEDB.4.0, C:db.mdb;admin;, Test) select id,name from Test INSERT 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 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=Excel 5.0).xactions 2、将Excel的数据导入SQL server : - = SELECT * into newtable FROM OpenDataSource( Microsoft.Jet.OLEDB.4.0, Data Source=c:book1.xls;User ID=Admin;Password=;Extended properties=Excel 5.0).Sheet1$ 实例: SELECT * into newtable FROM OpenDataSource( Microsoft.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 -Sservername -Usa -P 参数：S 是SQL服务器名；U是用户；P是密码 说明：还可以导出文本文件等多种格式 实例:EXEC master.xp_cmdshell bcp saletesttmp.dbo.CusAccount out c:temp1.xls -c -q -Spmserver -Usa -Psa EXEC master.xp_cmdshell bcp SELECT au_fname, au_lname FROM pubs.authors ORDER BY au_lname queryout C: authors.xls -c -Sservername -Usa -Ppassword 在VB6中应用ADO导出EXCEL文件代码： Dim cn As New ADODB.Connection 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 -Sservername -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,3) T-SQL代码： INSERT INTO OPENDATASOURCE(Microsoft.JET.OLEDB.4.0, Extended Properties=Excel 8.0;Data source=C:traininginventur.xls).Filiale1$ (bestand, produkt) VALUES (20, Test) 总结：利用以上语句，我们可以方便地将SQL SERVER、ACCESS和EXCEL电子表格软件中的数据进行转换，为我们提供了极大方便！ 回复 # re: SQL语句导入导出大全收集 2005-08-06 00:37 任搏软ASP导出Excel数据的四种方法 来源： aspsky 作者： tonny 一、使用OWC 什么是OWC？ OWC是Office Web Compent的缩写，即Microsoft的Office Web组件，它为在Web中绘制图形提供了灵活的同时也是最基本的机制。在一个intranet环境中，如果可以假设客户机上存在特定的浏览器和一些功能强大的软件（如IE5和Office 2000），那么就有能力利用Office Web组件提供一个交互式图形开发环境。这种模式下，客户端工作站将在整个任务中分担很大的比重。 %Option Explicit Class ExcelGen Private objSpreadsheet Private iColOffset Private iRowOffset Sub Class_Initialize() Set objSpreadsheet = Server.CreateObject(OWC.Spreadsheet) iRowOffset = 2 iColOffset = 2 End Sub Sub Class_Terminate() Set objSpreadsheet = Nothing Clean up End Sub Public Property Let ColumnOffset(iColOff) If iColOff 0 then iColOffset = iColOff Else iColOffset = 2 End If End Property Public Property Let RowOffset(iRowOff) If iRowOff 0 then iRowOffset = iRowOff Else iRowOffset = 2 End If End Property Sub GenerateWorksheet(objRS) Populates the Excel worksheet based on a Recordsets contents Start by displaying the titles If objRS.EOF then Exit Sub Dim objField, iCol, iRow iCol = iColOffset iRow = iRowOffset For Each objField in objRS.Fields objSpreadsheet.Cells(iRow, iCol).Value = objField.Name objSpreadsheet.Columns(iCol).AutoFitColumns 设置Excel表里的字体 objSpreadsheet.Cells(iRow, iCol).Font.Bold = True objSpreadsheet.Cells(iRow, iCol).Font.Italic = False objSpreadsheet.Cells(iRow, iCol).Font.Size = 10 objSpreadsheet.Cells(iRow, iCol).Halignment = 2 居中 iCol = iCol + 1 Next objField Display all of the data Do While Not objRS.EOF iRow = iRow + 1 iCol = iColOffset For Each objField in objRS.Fields If IsNull(objField.Value) then objSpreadsheet.Cells(iRow, iCol).Value = Else objSpreadsheet.Cells(iRow, iCol).Value = objField.Value objSpreadsheet.Columns(iCol).AutoFitColumns objSpreadsheet.Cells(iRow, iCol).Font.Bold = False objSpreadsheet.Cells(iRow, iCol).Font.Italic = False objSpreadsheet.Cells(iRow, iCol).Font.Size = 10 End If iCol = iCol + 1 Next objField objRS.MoveNext Loop End Sub Function SaveWorksheet(strFileName) Save the worksheet to a specified filename On Error Resume Next Call objSpreadsheet.ActiveSheet.Export(strFileName, 0) SaveWorksheet = (Err.Number = 0) End Function End Class Dim objRS Set objRS = Server.CreateObject(ADODB.Recordset) objRS.Open SELECT * FROM xxxx, Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xxxx;Password=xxxx;Initial Catalog=xxxx;Data source=xxxx; Dim SaveName SaveName = Request.Cookies(savename)(name) Dim objExcel Dim ExcelPath ExcelPath = Excel & SaveName & .xls Set objExcel = New ExcelGen objExcel.RowOffset = 1 objExcel.ColumnOffset = 1 objExcel.GenerateWorksheet(objRS) If objExcel.SaveWorksheet(Server.MapPath(ExcelPath) then Response.Write htmlbody bgcolor=gainsboro text=#000000已保存为Excel文件. a href= & server.URLEncode(ExcelPath) & 下载/a Else Response.Write 在保存过程中有错误! End If Set objExcel = Nothing objRS.Close Set objRS = Nothing % 二、用Excel的Application组件在客户端导出到Excel或Word 注意：两个函数中的“data“是网页中要导出的table的 id input type=hidden name=out_word onclick=vbscript:buildDoc value=导出到word class=notPrint input type=hidden name=out_excel onclick=AutomateExcel(); value=导出到excel class=notPrint 导出到Excel代码 SCRIPT LANGUAGE=javascript !- function AutomateExcel() / Start Excel and get Application object. var oXL = new ActiveXObject(Excel.Application); / Get a new workbook. var oWB = oXL.Workbooks.Add(); var oSheet = oWB.ActiveSheet; var table = document.all.data; var hang = table.rows.length; var lie = table.rows(0).cells.length; / Add table headers going cell by cell. for (i=0;ihang;i+) for (j=0;jlie;j+) oSheet.Cells(i+1,j+1).value = table.rows(i).cells(j).innerText; oXL.Visible = true; oXL.UserControl = true; /- /SCRIPT 导出到Word代码 script language=vbscript Sub buildDoc set table = document.all.data row = table.rows.length column = table.rows(1).cells.length Set objWordDoc = CreateObject(Word.Document) objWordDoc.Application.Documents.Add theTemplate, False objWordDoc.Application.Visible=True Dim theArray(20,10000) for i=0 to row-1 for j=0 to column-1 theArray(j+1,i+1) = table.rows(i).cells(j).innerTEXT next next objWordDoc.Application.ActiveDocument.Paragraphs.Add.Range.InsertBefore(综合查询结果集) /显示表格标题 objWordDoc.Application.ActiveDocument.Paragraphs.Add.Range.InsertBefore() Set rngPara = objWordDoc.Application.ActiveDocument.Paragraphs(1).Range With rngPara .Bold = True /将标题设为粗体 .ParagraphFormat.Alignment = 1 /将标题居中 .Font.Name = 隶书 /设定标题字体 .Font.Size = 18 /设定标题字体大小 End With Set rngCurrent = objWordDoc.Application.ActiveDocument.Paragraphs(3).Range Set tabCurrent = ObjWordDoc.Application.ActiveDocument.Tables.Add(rngCurrent,row,column) for i = 1 to column objWordDoc.Application.ActiveDocument.Tables(1).Rows(1).Cells(i).Range.InsertAfter theArray(i,1) objWordDoc.Application.ActiveDocument.Tables(1).Rows(1).Cells(i).Range.ParagraphFormat.alignment=1 next For i =1 to column For j = 2 to row objWordDoc.Application.ActiveDocument.Tables(1).Rows(j).Cells(i).Range.InsertAfter theArray(i,j) objWordDoc.Ap