MSSQL网页编程之SQL数据库导进导出数据代码年夜全
修复过程包含最多4个阶段，在下面描述。在你开始前，你应该cd到数据库目录和检查表文件的权限，确保他们可被运行mysqld的Unix用户读取(和你，因为你需要存取你正在检查的文件)。如果它拒绝你修改文件，他们也必须是可被你写入的。/*******导出到excelEXECmaster..xp_cmdshell’bcpSettleDB.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
SELECTcast(cast(科目编号asnumeric(10,2))asnvarchar(255))+’ ’转换后的别号
FROMOpenDataSource(’Microsoft.Jet.OLEDB.4.0’,
’DataSource="c: est.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0’)...xactions
/**导进文本文件
EXECmaster..xp_cmdshell’bcp"dbname..tablename"inc:DT.txt-c-Sservername-Usa-Ppassword’
/**导出文本文件
EXECmaster..xp_cmdshell’bcp"dbname..tablename"outc:DT.txt-c-Sservername-Usa-Ppassword’
或
EXECmaster..xp_cmdshell’bcp"Select*fromdbname..tablename"queryoutc:DT.txt-c-Sservername-Usa-Ppassword’
导出到TXT文本，用逗号分隔
execmaster..xp_cmdshell’bcp"库名..表名"out"d: t.txt"-c-t,-Usa-Ppassword’
BULKINSERT库名..表名
FROM’c: 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表)
*********************导进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,
commentntext’text()’)
EXECsp_xml_removedocument@idoc
/********************导全部数据库*********************************************/
用bcp完成的存储历程
/*
完成数据导进/导出的存储历程
依据分歧的参数,能够完成导进/导出全部数据库/单个表
挪用示例:
--导出挪用示例
----导出单个表
execfile2table’zj’,’’,’’,’xzkh_sa..区域材料’,’c:zj.txt’,1
----导出全部数据库
execfile2table’zj’,’’,’’,’xzkh_sa’,’C:docman’,1
--导进挪用示例
----导进单个表
execfile2table’zj’,’’,’’,’xzkh_sa..区域材料’,’c:zj.txt’,0
----导进全部数据库
execfile2table’zj’,’’,’’,’xzkh_sa’,’C:docman’,0
*/
ifexists(select1fromsysobjectswherename=’File2Table’andobjectproperty(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=1then’out’else’in’end
+’"’+@filename+’"/w’
+’/S’+@servername
+casewhenisnull(@username,’’)=’’then’’else’/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=1then’out’else’in’end
+’"’+@filename+@m_tbname+’.txt"/w’
+’/S’+@servername
+casewhenisnull(@username,’’)=’’then’’else’/U’+@usernameend
+’/P’+isnull(@password,’’)
execmaster..xp_cmdshell@sql
fetchnextfrom#tbinto@m_tbname
end
close#tb
deallocate#tb
end
go
/**********************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$)
)...
假如你想间接拔出并天生文本文件,就要用bcp
declare@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将文件导进导出到数据库的存储历程:
/*--bcp-二进制文件的导进导出
撑持image,text,ntext字段的导进/导出
image合适于二进制文件;text,ntext合适于文本数据文件
注重:导进时,将掩盖满意前提的一切行
导出时,将把一切满意前提的行也出到指定文件中
此存储历程仅用bcp完成
邹建2003.08-----------------*/
/*--挪用示例
--数据导出
execp_binaryIO’zj’,’’,’’,’acc_演示数据..tb’,’img’,’c:zj1.dat’
--数据导出
execp_binaryIO’zj’,’’,’’,’acc_演示数据..tb’,’img’,’c:zj1.dat’,’’,0
--*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N’.’)andOBJECTPROPERTY(id,N’IsProcedure’)=1)
dropprocedure.
GO
Createprocp_binaryIO
@servenamevarchar(30),--服务器称号
@usernamevarchar(30),--用户名
@passwordvarchar(30),--暗码
@tbnamevarchar(500),--数据库..表名
@fdnamevarchar(30),--字段名
@fnamevarchar(1000),--目次+文件名,处置过程当中要利用/掩盖:@filename+.bak
@tjvarchar(1000)=’’,--处置前提.关于数据导进,假如前提中包括@fdname,请指定表名前缀
@isoutbit=1--1导出((默许),0导进
AS
declare@fname_invarchar(1000)--bcp处置应对文件名
,@fsizevarchar(20)--要处置的文件的巨细
,@m_tbnamevarchar(50)--一时表名
,@sqlvarchar(8000)
--则获得导进文件的巨细
if@isout=1
set@fsize=’0’
else
begin
createtable#tb(可选名varchar(20),巨细int
,创立日期varchar(10),创立工夫varchar(20)
,前次写操纵日期varchar(10),前次写操纵工夫varchar(20)
,前次会见日期varchar(10),前次会见工夫varchar(20),特征int)
insertinto#tb
execmaster..xp_getfiledetails@fname
select@fsize=巨细from#tb
droptable#tb
if@fsizeisnull
begin
print’文件未找到’
return
end
end
--天生数据处置应对文件
set@m_tbname=’[##temp’+cast(newid()asvarchar(40))+’]’
set@sql=’select*into’+@m_tbname+’from(
selectnullas范例
unionallselect0as前缀
unionallselect’+@fsize+’as长度
unionallselectnullas停止
unionallselectnullas格局
)a’
exec(@sql)
select@fname_in=@fname+’_temp’
,@sql=’bcp"’+@m_tbname+’"out"’+@fname_in
+’"/S"’+@servename
+casewhenisnull(@username,’’)=’’then’’
else’"/U"’+@usernameend
+’"/P"’+isnull(@password,’’)+’"/c’
execmaster..xp_cmdshell@sql
--删除一时表
set@sql=’droptable’+@m_tbname
exec(@sql)
if@isout=1
begin
set@sql=’bcp"selecttop1’+@fdname+’from’
+@tbname+caseisnull(@tj,’’)when’’then’’
else’where’+@tjend
+’"queryout"’+@fname
+’"/S"’+@servename
+casewhenisnull(@username,’’)=’’then’’
else’"/U"’+@usernameend
+’"/P"’+isnull(@password,’’)
+’"/i"’+@fname_in+’"’
execmaster..xp_cmdshell@sql
end
else
begin
--为数据导进筹办一时表
set@sql=’selecttop0’+@fdname+’into’
+@m_tbname+’from’+@tbname
exec(@sql)
--将数据导进光临时表
set@sql=’bcp"’+@m_tbname+’"in"’+@fname
+’"/S"’+@servename
+casewhenisnull(@username,’’)=’’then’’
else’"/U"’+@usernameend
+’"/P"’+isnull(@password,’’)
+’"/i"’+@fname_in+’"’
execmaster..xp_cmdshell@sql
--将数据导进到正式表中
set@sql=’update’+@tbname
+’set’+@fdname+’=b.’+@fdname
+’from’+@tbname+’a,’
+@m_tbname+’b’
+caseisnull(@tj,’’)when’’then’’
else’where’+@tjend
exec(@sql)
--删除数据处置一时表
set@sql=’droptable’+@m_tbname
end
--删除数据处置应对文件
set@sql=’del’+@fname_in
execmaster..xp_cmdshell@sql
go
/**导进文本文件
EXECmaster..xp_cmdshell’bcp"dbname..tablename"inc:DT.txt-c-Sservername-Usa-Ppassword’
改成以下，不需引号
EXECmaster..xp_cmdshell’bcpdbname..tablenameinc:DT.txt-c-Sservername-Usa-Ppassword’
/**导出文本文件
EXECmaster..xp_cmdshell’bcp"dbname..tablename"outc:DT.txt-c-Sservername-Usa-Ppassword’
此句需加引号
mysqld进程在一个写入当中被杀死;计算机的意外关闭(例如，如果计算机掉电);一个硬件错误。 我们学到了什么？思考问题的时候从表的角度来思考问 可以动态传入参数，省却了动态SQL的拼写。 以前的DTS轻盈简单。但是现在的SSIS虽然功能强大了很多，但是总是让人感觉太麻烦。看看论坛中询问SSIS的贴子就知道。做的功能太强大了，往往会有很多用户不会用了 不好!如果出了错;不好调试;不好处理!其实web开发将代码分为3层:web层;业务逻辑层和数据访问层;一般对数据库的操作都在数据访问层来做;这样便于调试和维护!而且将来如果是换了数据库的话;你只需要改数据层的代码;其他层的基本可以不变!要是你在jsp中直接调用sql数据库;那么如果换了数据库呢?岂不都要改?如果报了异常呢?怎么做异常处理? 数据库物理框架没有变动undo和redo都放在数据库得transaction中，个人感觉是个败笔。如果说我们在设计数据库的时候考虑分多个数据库，可能能在一定程度上避免I/O效率问题。 学习SQL语言的话如果要学会去做网站就不是很难!但是要做数据库管理的话就有难度了!
页:
[1]