SQL?Server?SQL语句导入导出大全(2)
发布时间：2020-12-12 02:23:07 所属栏目：MySql教程 来源：网络整理
导读：*********************??导入?xml 文件? DECLARE?@idoc?int? DECLARE?@doc?varchar(1000)? --sample?XML?document? SET?@doc?=’? root? ??Customer?cid=?"C1"?name="Janine"?city="Issaquah"? ??????Order?oid="O1"?date="1/20/1996"?amount="3.5"?/? ??????
|
*********************??导入?xml 文件? DECLARE?@idoc?int? DECLARE?@doc?varchar(1000)? --sample?XML?document? SET?@doc?=’? <root>? ??<Customer?cid=?"C1"?name="Janine"?city="Issaquah">? ??????<Order?oid="O1"?date="1/20/1996"?amount="3.5"?/>? ??????<Order?oid="O2"?date="4/30/1997"?amount="13.4">Customer?was?very?satisfied? ??????</Order>? ???</Customer>? ???<Customer?cid="C2"?name="Ursula"?city="Oelde"?>? ??????<Order?oid="O3"?date="7/14/1999"?amount="100"?note="Wrap?it?blue? ?????????????white?red">? ????????????<Urgency>Important</Urgency>? ????????????Happy?Customer.? ??????</Order>? ??????<Order?oid="O4"?date="1/20/1996"?amount="10000"/>? ???</Customer>? </root>? ’? --?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? /********************导整个数据库*********************************************/? 用bcp实现的存储过程? /*? ?实现数据导入/导出的存储过程? ?????????根据不同的参数,可以实现导入/导出整个数据库/单个表? ?调用示例:? --导出调用示例? ----导出单个表? exec?file2table?’zj’,’’,’xzkh_sa..地区资料’,’c:zj.txt’,1? ----导出整个数据库? exec?file2table?’zj’,’xzkh_sa’,’C:docman’,1? --导入调用示例? ----导入单个表? exec?file2table?’zj’,0? ----导入整个数据库? exec?file2table?’zj’,0? */? if?exists(select?1?from?sysobjects?where?name=’File2Table’?and?objectproperty(id,’IsProcedure’)=1)? ?drop?procedure?File2Table? go? create?procedure?File2Table? @servername?varchar(200)??--服务器名? ,@username?varchar(200)???--用户名,如果用NT验证方式,则为空’’? ,@password?varchar(200)???--密码? ,@tbname?varchar(500)???--数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表? ,@filename?varchar(1000)??--导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt? ,@isout?bit??????--1为导出,0为导入? as? declare?@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?@sql? end? else? begin?--导出整个数据库,定义游标,取出所有的用户表? ?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?? end? go? （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |