MSSQL编程：导进导出数据语句小结 EXCEL,Excel,动态

冷月葬花魂 发表于 2015-1-16 22:33:39

MSSQL编程：导进导出数据语句小结

支持AIX、FreeBSD、HP-UX、Linux、MacOS、NovellNetware、OpenBSD、OS/2Wrap、Solaris、Windows等多种操作系统导出数据|语句/*导出到excel
EXECmaster..xp_cmdshellbcpSettleDB.dbo.shanghuoutc: emp1.xls-c-q-S"GNETDATA/GNETDATA"-U"sa"-P""/*导进Excel
SELECT*
FROMOpenDataSource(Microsoft.Jet.OLEDB.4.0,
DataSource="c: est.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0)...xactions/*静态文件名
declare@fnvarchar(20),@svarchar(1000)
set@fn=c: est.xls
set@s=Microsoft.Jet.OLEDB.4.0,
DataSource="+@fn+";UserID=Admin;Password=;Extendedproperties=Excel5.0
set@s=SELECT*FROMOpenDataSource(+@s+)...sheet1$
exec(@s)
*/SELECTcast(cast(科目编号asnumeric(10,2))asnvarchar(255))+　转换后的别号
FROMOpenDataSource(Microsoft.Jet.OLEDB.4.0,
DataSource="c: est.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0)...xactions/**********************EXCEL导到远程SQL
insertOPENDATASOURCE(
SQLOLEDB,
DataSource=远程ip;UserID=sa;Password=暗码
).库名.dbo.表名(列名1,列名2)
SELECT列名1,列名2
FROMOpenDataSource(Microsoft.Jet.OLEDB.4.0,
DataSource="c: est.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0)...xactions
/**导进文本文件
EXECmaster..xp_cmdshellbcpdbname..tablenameinc:DT.txt-c-Sservername-Usa-Ppassword/**导出文本文件
EXECmaster..xp_cmdshellbcpdbname..tablenameoutc:DT.txt-c-Sservername-Usa-Ppassword
或
EXECmaster..xp_cmdshellbcp"Select*fromdbname..tablename"queryoutc:DT.txt-c-Sservername-Usa-Ppassword导出到TXT文本，用逗号分隔
execmaster..xp_cmdshellbcp"库名..表名"out"d: t.txt"-c-t,-Usa-Ppassword
BULKINSERT库名..表名
FROMc: est.txt
WITH(
FIELDTERMINATOR=;,
ROWTERMINATOR=

)
--/*dBaseIV文件
select*from
OPENROWSET(MICROSOFT.JET.OLEDB.4.0
,dBaseIV;HDR=NO;IMEX=2;DATABASE=C:,select*from[客户材料4.dbf])
--*/--/*dBaseIII文件
select*from
OPENROWSET(MICROSOFT.JET.OLEDB.4.0
,dBaseIII;HDR=NO;IMEX=2;DATABASE=C:,select*from[客户材料3.dbf])
--*/--/*FoxPro数据库
select*fromopenrowset(MSDASQL,
Driver=MicrosoftVisualFoxProDriver;SourceType=DBF;SourceDB=c:,
select*from)
--*//**************导进DBF文件****************/
select*fromopenrowset(MSDASQL,
Driver=MicrosoftVisualFoxProDriver;
SourceDB=e:VFP98data;
SourceType=DBF,
select*fromcustomerwherecountry!="USA"orderbycountry)
go
/*****************导出到DBF***************/
假如要导出数据到已天生布局(即现存的)FOXPRO表中,能够间接用上面的SQL语句insertintoopenrowset(MSDASQL,
Driver=MicrosoftVisualFoxProDriver;SourceType=DBF;SourceDB=c:,
select*from)
select*from表申明:
SourceDB=c:指定foxpro表地点的文件夹
aa.DBF指定foxpro表的文件名.
/*************导出到Access********************/
insertintoopenrowset(Microsoft.Jet.OLEDB.4.0,
x:A.mdb;admin;,A表)select*from数据库名..B表/*************导进Access********************/
insertintoB表selet*fromopenrowset(Microsoft.Jet.OLEDB.4.0,
x:A.mdb;admin;,A表)文件名为参数
declare@fnamevarchar(20)
set@fname=d: est.mdb
exec(SELECTa.*FROMopendatasource(Microsoft.Jet.OLEDB.4.0,
+@fname+;admin;,topics)asa)SELECT*
FROMOpenDataSource(Microsoft.Jet.OLEDB.4.0,
DataSource="f:
orthwind.mdb";JetOLEDB:DatabasePassword=123;UserID=Admin;Password=;)...产物*********************导进xml　文件DECLARE@idocint
DECLARE@docvarchar(1000)
--sampleXMLdocument
SET@doc=
<root>
<Customercid="C1"name="Janine"city="Issaquah">
<Orderoid="O1"date="1/20/1996"amount="3.5"/>
<Orderoid="O2"date="4/30/1997"amount="13.4">Customerwasverysatisfied
</Order>
</Customer>
<Customercid="C2"name="Ursula"city="Oelde">
<Orderoid="O3"date="7/14/1999"amount="100"note="Wrapitblue
whitered">
<Urgency>Important</Urgency>
HappyCustomer.
</Order>
<Orderoid="O4"date="1/20/1996"amount="10000"/>
</Customer>
</root>

--CreateaninternalrepresentationoftheXMLdocument.
EXECsp_xml_preparedocument@idocOUTPUT,@doc--ExecuteaSELECTstatementusingOPENXMLrowsetprovider.
SELECT*
FROMOPENXML(@idoc,/root/Customer/Order,1)
WITH(oidchar(5),
amountfloat,
commentntexttext())
EXECsp_xml_removedocument@idoc???????/**********************Excel导到Txt****************************************/
想用
select*intoopendatasource(...)fromopendatasource(...)
完成将一个Excel文件内容导进到一个文本文件假定Excel中有两列，第一列为姓名，第二列为很行帐号(16位)
且银行帐号导出到文本文件后分两部分，前8位和后8位分隔。
邹健：
假如要用你下面的语句拔出的话,文本文件必需存在,并且有一行:姓名,银行账号1,银行账号2
然后就能够用上面的语句举行拔出
注重文件名和目次依据你的实践情形举行修正.insertinto
opendatasource(MICROSOFT.JET.OLEDB.4.0
,Text;HDR=Yes;DATABASE=C:
)...
--,aa#txt)
--*/
select姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
from
opendatasource(MICROSOFT.JET.OLEDB.4.0
,Excel5.0;HDR=YES;IMEX=2;DATABASE=c:a.xls
--,Sheet1$)
)...假如你想间接拔出并天生文本文件,就要用bcpdeclare@sqlvarchar(8000),@tbnamevarchar(50)--起首将excel表内容导进到一个全局一时表
select@tbname=[##temp+cast(newid()asvarchar(40))+]
,@sql=select姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
into+@tbname+from
opendatasource(MICROSOFT.JET.OLEDB.4.0
,Excel5.0;HDR=YES;IMEX=2;DATABASE=c:a.xls
)...
exec(@sql)--然后用bcp从全局一时表导出到文本文件
set@sql=bcp"+@tbname+"out"c:aa.txt"/S"(local)"/P""/c
execmaster..xp_cmdshell@sql--删除一时表
exec(droptable+@tbname)
/********************导全部数据库*********************************************/用bcp完成的存储历程
/*
完成数据导进/导出的存储历程
依据分歧的参数,能够完成导进/导出全部数据库/单个表
挪用示例:
--导出挪用示例
----导出单个表
execfile2tablezj,,,xzkh_sa..区域材料,c:zj.txt,1
----导出全部数据库
execfile2tablezj,,,xzkh_sa,C:docman,1--导进挪用示例
----导进单个表
execfile2tablezj,,,xzkh_sa..区域材料,c:zj.txt,0
----导进全部数据库
execfile2tablezj,,,xzkh_sa,C:docman,0*/
ifexists(select1fromsysobjectswherename=File2Tableandobjectproperty(id,IsProcedure)=1)
dropprocedureFile2Table
go
createprocedureFile2Table
@servernamevarchar(200)--服务器名
,@usernamevarchar(200)--用户名,假如用NT考证体例,则为空
,@passwordvarchar(200)--暗码
,@tbnamevarchar(500)--数据库.dbo.表名,假如不指定:.dbo.表名,则导出数据库的一切用户表
,@filenamevarchar(1000)--导进/导前路径/文件名,假如@tbname参数指明是导出全部数据库,则这个参数是文件寄存路径,文件名主动用表名.txt
,@isoutbit--1为导出,0为导进
as
declare@sqlvarchar(8000)if@tbnamelike%.%.%--假如指定了表名,则间接导出单个表
begin
set@sql=bcp+@tbname
+casewhen@isout=1thenoutelseinend
+"+@filename+"/w
+/S+@servername
+casewhenisnull(@username,)=thenelse/U+@usernameend
+/P+isnull(@password,)
execmaster..xp_cmdshell@sql
end
else
begin--导出全部数据库,界说游标,掏出一切的用户表
declare@m_tbnamevarchar(250)
ifright(@filename,1)set@filename=@filename+set@m_tbname=declare#tbcursorforselectnamefrom+@tbname+..sysobjectswherextype=U
exec(@m_tbname)
open#tb
fetchnextfrom#tbinto@m_tbname
while@@fetch_status=0
begin
set@sql=bcp+@tbname+..+@m_tbname
+casewhen@isout=1thenoutelseinend
+"+@filename+@m_tbname+.txt"/w
+/S+@servername
+casewhenisnull(@username,)=thenelse/U+@usernameend
+/P+isnull(@password,)
execmaster..xp_cmdshell@sql
fetchnextfrom#tbinto@m_tbname
end
close#tb
deallocate#tb
end
go
/*************Oracle**************/
EXECsp_addlinkedserverOracleSvr,
Oracle7.3,
MSDAORA,
ORCLDB
GOdeletefromopenquery(mailser,select*fromyulin)select*fromopenquery(mailser,select*fromyulin)updateopenquery(mailser,select*fromyulinwhereid=15)setdisorder=555,catago=888insertintoopenquery(mailser,selectdisorder,catagofromyulin)values(333,777)增补：关于用bcp导出,是没有字段名的.用openrowset导出,必要事前建好表.用openrowset导进,除ACCESS及EXCEL外,均不撑持非本机数据导进MySQL是一个开放源码的小型关联式数据库管理系统，开发者为瑞典MySQLAB公司。目前MySQL被广泛地应用在Internet上的中小型网站中。

灵魂腐蚀 发表于 2015-1-17 16:17:33

一直以来个人感觉SQLServer的优化器要比Oracle的聪明。SQL2005的更是比2k聪明了不少。（有次作试验发现有的语句在200万级时还比50万级的相同语句要快show_text的一些提示没有找到解释。一直在奇怪。）

金色的骷髅 发表于 2015-1-20 20:25:17

而写到本地,我又考虑到效率问题.大家来讨论讨论吧,分数不打紧,就给10分,十全十美,没啥对错,各抒己见,但是要有说服力的哦~

蒙在股里 发表于 2015-1-29 20:52:12

可以动态传入参数，省却了动态SQL的拼写。

精灵巫婆 发表于 2015-2-6 04:45:54

我是一个ERP初学者，对于前台运用基本熟悉，但对于后台SQLServer的运用一点也不懂，特想学习下相关资料。至少懂得一些基本的运用。希望各位能给于建议，小弟再谢过！

若天明 发表于 2015-2-15 13:50:37

理解了存储结构，再阅读下性能优化的章节基本上会对sqlserver有个清晰地认识

飘飘悠悠 发表于 2015-3-4 11:34:40

XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！）

变相怪杰 发表于 2015-3-11 19:16:59

代替了原来VB式的错误判断。比Oracle高级不少。

乐观发表于 2015-3-19 09:17:21

总感觉自己还是不会SQL

小女巫 发表于 2015-3-27 18:09:31

理解了存储结构，再阅读下性能优化的章节基本上会对sqlserver有个清晰地认识

页: [1]

仓酷云's Archiver

MSSQL编程：导进导出数据语句小结