SNH48debindebshow印子月MV&4;&5;&5;这里是当代舞的部分作品，全集请点击[]下次自动登录
现在的位置:
& 综合 & 正文
SqlServer，DBF两种格式的转化问题做个总结
这里，就SqlServer，DBF两种格式的转化问题做个总结。
一： 从dBase文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:
/*===================================================================*/
--如果接受数据导入的表已经存在
insert into 表 select * from
openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=c:\','select * from [test.dbf]')
--如果导入数据并生成表
select * into 表 from
openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=c:\','select * from [test.dbf]')
/*===================================================================*/
--如果从SQL数据库中,导出数据到dBase,如果dBase文件已经存在,就可以简单的用:
insert into
openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=c:\','select * from [test.dbf]')
select * from 表
DATABASE=c:\
c:\是dbf文件的存放目录
'select * from [test.dbf]
test.dbf是指dbf文件名
二 如果dBase文件不存在,就需要用到下面的存储过程了.
/*--数据导出dBase
导出表中的数据到dBase,如果文件不存在,将自动创建文件
基于通用性考虑,仅支持导出标准数据类型
/*--调用示例
--导出dBase
p_exporttb @tbname='地区资料',@path='c:\',@over=0
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_exporttb]
create proc p_exporttb
@tbname sysname,
--要导出的表名
@path nvarchar(1000),
--文件存放目录
@fname nvarchar(250)='',
--文件名,默认为表名
@over bit=0
--是否覆盖已经存在的文件,如果不覆盖,则直接追加
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
--参数检测
if isnull(@fname,'')='' set @fname=@tbname+'.dbf'
--检查文件是否已经存在
if right(@path,1)&&'\' set @path=@path+'\'
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
if exists(select 1 from #tb where a=1)
if @over=1
set @sql='del '+@sql
exec master..xp_cmdshell @sql,no_output
set @over=0
set @over=1
--数据库创建语句
set @sql=@path+@fname
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="dBASE 5.0;'
+';HDR=NO;DATABASE='+@path+'"'
--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err&&0 goto lberr
exec @err=sp_oamethod @obj,'open',null,@constr
if @err&&0 goto lberr
--创建表的SQL
select @sql='',@fdlist=''
select @fdlist=@fdlist+','+a.name
,@sql=@sql+',['+a.name+'] '
+case when b.name in('char','nchar','varchar','nvarchar') then
'text('+cast(case when a.length&250 then 250 else a.length end as varchar)+')'
when b.name in('tynyint','int','bigint','tinyint') then 'int'
when b.name in('smalldatetime','datetime') then 'datetime'
when b.name in('money','smallmoney') then 'money'
else b.name end
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
and object_id(@tbname)=id
select @sql='create table ['+@fname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)
if @over=1
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err&&0 goto lberr
exec @err=sp_oadestroy @obj
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''dBase 5.0;DATABASE='
+@path+''',''select * from ['+@fname+']'')'
--导入数据
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname)
exec sp_oageterrorinfo 0,@src out,@desc out
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist
两个很常用的存储过程
1 用于产生10条评论数据
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
/**//*存储过程，用于产生10条评论数据*/
-- -- -- -- delete from reviews where operateID&15
-- -- -- --
-- -- -- -- delete from contentinfos where ObjectType=5
proc Create100Comments
declare @i int
declare @ContentID bigint
while @i&=10
BEGIN TRAN
if( @@error != 0 )
goto ErrorHandler
commit tran
INSERT INTO [ContentInfos]
( CategoryID,Source,
ObjectType,ClickCount,
ReviewCount,Grade,
VoteCount,CommendCount,
AuthorID,BlogID,
Size,CollectionTime,
IsActive,Keyword,
CollectionUser,OriginalID,
OriginalURL,F1,
54,204562,
10000,getdate(),
'testuser',999,
'testurl',100,
SET @ContentID = IDENT_CURRENT('ContentInfos')
INSERT INTO
Reviews (CellID ,
PortalID ,
ContentID ,
OperateTitle ,
OperateContent ,
OperateDateTime ,
BlogTitle ,
OperateUserID ,
UserName ,
UserNick ,
CBlogName ,
OperateEmail ,
OperateIP ,
IsAnonymous ,
IsActive ,
OperateHomePage ,
OperateType ,
OperateValue )
VALUES(@i, @i, @i, @ContentID, '操作标题', '评论内容1&br&评论内容2&br&评论内容1&br&&br&&br&& br&评论内容2&br&&br&&br&&br&&br&评论内容2& br&&br&&br&&br&&br&评论内容4&br&', getdate(), @i, 'Blog标题', @i, '用户名称', '用户呢称', 'Blog名称', '', '124.458.135.500', 0, 1, '', 1, @i)
if( @@error != 0 )
goto ErrorHandler
ErrorHandler:
if( @@error != 0 )
rollback tran
set @i=@i+1
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
2 产生分页的存储过程
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
/**//****** Object:
Stored Procedure dbo.CPP_GetTheLastSchoolsWithPage
Script Date:
10:40:08 ******/
/**//* ##SUMMARY 查询一个学校的话题或者活动等 */
-- ##REMARKS Authors :wht
-- ##PARAM @PageSize
-- ##PARAM @PageIndex
-- ##PARAM @RowCount
--CPP_GetSchoolThemeWithPageNew 10,0,100
create procedure CPP_Getthesis_thesisWithPageNew
@PageIndex
----@whereClauses varchar(1000)
DECLARE @SQL VARCHAR(5000)
declare @PageCount int
declare @currentPageSize
--计算总页数
SET @PageCount=CASE WHEN @RowCount%@PageSize=0 THEN @RowCount/@PageSize ELSE @RowCount/@PageSize+1 END
SET @PageIndex=@PageIndex+1
IF @PageIndex&=1
set @SQL='select SchoolName,t.* from (SELECT top '+cast(@PageSize as varchar(10))+' ID, Title, Context, CreateTime, BlogID, AuthorBlogName, Author, Type, IsTop, ReplyCount, ReplyTime, ReplyContext, ReplyBlogID, ReplyBlogName, ReplyNickName, SchoolID, SchoolRegionname
School_Themes
order by id desc)t inner join school v on t.SchoolID = v.SchoolID order by id desc'
--最后一页
IF @PageIndex&=@PageCount OR @PageIndex&=0
set @currentPageSize= @RowCount-(@PageCount-1)*@PageSize
if(@currentPageSize&=0)
set @currentPageSize=@PageSize
SET @SQL='select SchoolName,t.*
from (SELECT top '+cast(@PageSize as varchar(10))+'ID, Title, Context, CreateTime, BlogID, AuthorBlogName, Author, Type, IsTop, ReplyCount, ReplyTime, ReplyContext, ReplyBlogID, ReplyBlogName, ReplyNickName, SchoolID, SchoolRegionname
School_Themes
order by id desc)t inner join school v on t.SchoolID = v.SchoolID order by id desc'
--中间页（上）
IF @PageIndex&1 AND @PageIndex&=@PageCount/2+1
SET @SQL='
SELECT TOP '+CONVERT(VARCHAR(15),@PageSize)+' SchoolName, t.*
SELECT TOP '+CONVERT(VARCHAR(15),@PageSize)+' ID, Title, Context, CreateTime, BlogID, AuthorBlogName, Author, Type, IsTop, ReplyCount, ReplyTime, ReplyContext, ReplyBlogID, ReplyBlogName, ReplyNickName, SchoolID, SchoolRegionname
SELECT TOP '+CONVERT(VARCHAR(15),@pageSize*@pageIndex)+'
ID, Title, Context, CreateTime, BlogID, AuthorBlogName, Author, Type, IsTop, ReplyCount, ReplyTime, ReplyContext, ReplyBlogID, ReplyBlogName, ReplyNickName, SchoolID, SchoolRegionname
School_Themes
order by id desc
order by id asc
inner join school v on t.SchoolID = v.SchoolID
order by id desc'
--中间页（下）
SET @SQL='SELECT TOP '+CONVERT(VARCHAR(15),@PageSize)+' shoolName, t.*
SELECT TOP '+CONVERT(VARCHAR(15),@rowCount - @pageSize * @pageIndex+@pageSize)+' ID, Title, Context, CreateTime, BlogID, AuthorBlogName, Author, Type, IsTop, ReplyCount, ReplyTime, ReplyContext, ReplyBlogID, ReplyBlogName, ReplyNickName, SchoolID, SchoolRegionname
School_Themes
order by id asc
inner join school v on t.SchoolID = v.SchoolID
order id desc
print @SQL
EXEC (@SQL)
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
3、根据存储过程名取存储过程内容
根据存储过程名取存储过程内容
-- 　调试　GetContentByProcedureName '[dbo].[CPP_CountOfTrdeByUserID]'
alter procedure GetContentByProcedureName
(@ProcedureName nvarchar(500))
if exists (select * from dbo.syscomments where id=object_id(N''+@ProcedureName+''))
select c.text, c.encrypted, c.number, xtype=convert(nchar(2), o.xtype),
datalength(c.text), convert(varbinary(8000), c.text), 0 from dbo.syscomments c, dbo.sysobjects o
where o.id = c.id and c.id = object_id(N''+@ProcedureName+'')
order by c.number, c.colid option(robust plan)
第一步：创建一个类clsTurnPicture
增加方法如下　：
/**//// &summary&
/// 播放图片类(downmoon)
/// &/summary&
/// &param name="arrImgName"&arrImgName&/param&
/// &param name="arrDesc"&arrDesc&/param&
/// &param name="strShopName"&strShopName&/param&
/// &param name="strBrandName"&strBrandName&/param&
/// &param name="strResoourceCode"&strResoourceCode&/param&
/// &param name="ScrWidth"&ScrWidth&/param&
/// &param name="ScrHeight"&ScrHeight&/param&
/// &param name="PicWidth"&PicWidth&/param&
/// &param name="PicHeight"&PicHeight&/param&
/// &param name="TextHeight"&TextHeight&/param&
/// &param name="ToolBarHeight"&ToolBarHeight&/param&
/// &param name="FontSize"&默认为16,px&/param&
/// &returns&&/returns&
public string getPicsForHtml(ArrayList arrImgName,ArrayList arrDesc,string strShopName,string strBrandName,string strResoourceCode,int ScrWidth,int ScrHeight,int PicWidth,int PicHeight,int TextHeight,int ToolBarHeight,int FontSize)
StringBuilder sb = new StringBuilder();
string strHtml="";
if(arrImgName==null) return "" ;
ArrayList ar1=new ArrayList(arrImgName);
ArrayList ar2=new ArrayList(arrDesc);
int len=ar1.C
int len2=ar1.Count-1;
int len3=ToolBarHeight-2;
string str1="";
string str2="";
//strHtm+=@"
&html&&head&&title&Buynow{0}-{1}-{2}图片信息&/title&
strHtml=@"&!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"" &&HTML&&HEAD&&title&"+
strShopName+"-"+strBrandName+"-"+"席位："+strResoourceCode+
"-外观展示图"+
@"&/title&
&meta http-equiv=""content-type"" content=""text/ charset=gb2312""&
&meta name=""GENERATOR"" Content=""Microsoft Visual Studio .NET 7.1""&
&meta name=""CODE_LANGUAGE"" Content=""C#""&
&meta name=""vs_defaultClientScript"" content=""JavaScript""&
&meta name=""vs_targetSchema"" content=""/intellisense/ie5""&
"+System.Environment.NewL
sb.Append(strHtml);
sb.Append("&script language=JavaScript&"+" ");
sb.Append("&!-- Begin"+System.Environment.NewLine);
sb.Append("var interval = 6; // delay between rotating images (in seconds)"+System.Environment.NewLine);
sb.Append("interval *= 1000;"+System.Environment.NewLine);
sb.Append("var flagtime=0;"+System.Environment.NewLine);
sb.Append("var image_index = -1;"+System.Environment.NewLine);
sb.Append("image_list = new Array();"+System.Environment.NewLine);
sb.Append("note_list = new noteArray("+len2.ToString()+")"+System.Environment.NewLine);
sb.Append("var number_of_image = image_list."+System.Environment.NewLine);
sb.Append("var timesnum=0;
"+System.Environment.NewLine);
//strHtml=string.Format(strHtml,len.ToString());
string strTemp="";
for(int k=0;k&ar1.Ck++)
strTemp=ar1[k].ToString();
//strHtm+=" image_list[k] = new imageItem("+strTemp+")";
str1+=" image_list["+k.ToString()+"] = new imageItem(""+strTemp+"")"+"; ";
sb.Append(""+System.Environment.NewLine);
for(int k=0;k&ar2.Ck++)
strTemp=strShopName+"-"+strBrandName+"-"+strResoourceCode+"-"+ar2[k].ToString();
str2+=" note_list["+k.ToString()+"] = ""+strTemp+"""+"; ";
sb.Append(str1);
sb.Append(str2);
sb.Append("function noteArray(size)"+System.Environment.NewLine);
sb.Append("{"+System.Environment.NewLine);
sb.Append("this.length="+System.Environment.NewLine);
sb.Append("for(i=1;i&=i++)"+System.Environment.NewLine);
sb.Append("{"+System.Environment.NewLine);
sb.Append(@"this[i]="""""+System.Environment.NewLine);
sb.Append("
}"+System.Environment.NewLine);
sb.Append(""+System.Environment.NewLine);
sb.Append("}"+System.Environment.NewLine);
sb.Append("function counternum()"+System.Environment.NewLine);
sb.Append("{"+System.Environment.NewLine);
sb.Append("if( timesnum & "+len2.ToString()+")"+System.Environment.NewLine);
sb.Append("{"+System.Environment.NewLine);
sb.Append("
timesnum++;"+System.Environment.NewLine);
sb.Append(@"//document.all.slide_ent.src=""about:blank"";");
sb.Append(System.Environment.NewLine);
sb.Append(@"//document.all.slide_ent.src=""about:blank"";");
sb.Append(System.Environment.NewLine);
sb.Append("
}"+System.Environment.NewLine);
sb.Append("}"+System.Environment.NewLine);
sb.Append(@"function run(obj)
interval=obj.options[obj.selectedIndex].value*1000;
rotateImage();
sb.Append(@"function stop()
interval=0;
function previous()
var new_image = getPreviousImage();
document[""rImage""].src = new_
function next()
var new_image = getNextImage();
document[""rImage""].src = new_
function imageItem(image_location)
this.image_item = new Image();
this.image_item.src = image_
function noteItem(note)
this.note_item = new Image();
this.note_item.src =
function get_ImageItemLocation(imageObj)
return(imageObj.image_item.src)
function get_NoteItemLocation(noteObj)
return(noteObj.note_item.src)
function generate(x, y)
var range = y - x + 1;
return Math.floor(Math.random() * range) +
sb.Append(@"function getPreviousImage()
image_index = image_index-1;
if (image_index & 0)
sb.Append("image_index=image_index+"+len.ToString()+";");
sb.Append(@"}
var new_image = get_ImageItemLocation(image_list[image_index]);
document.all.span_1.innerText=image_index+1;
document.all.summary.innerText=note_list[image_index];
return(new_image);");
sb.Append(System.Environment.NewLine+"}");
sb.Append(@"function getNextImage()
image_index = image_index+1;
sb.Append("if (image_index &= "+len.ToString()+")");
sb.Append(System.Environment.NewLine);
sb.Append(@"{");
sb.Append(System.Environment.NewLine);
sb.Append("image_index=image_index-"+len.ToString()+";");
sb.Append(System.Environment.NewLine);
sb.Append("}");
sb.Append(System.Environment.NewLine);
//sb.Append(@"");
sb.Append(@"var new_image = get_ImageItemLocation(image_list[image_index]);
document.all.span_1.innerText=image_index+1;
document.all.summary.innerText=note_list[image_index];
return(new_image);");
sb.Append(System.Environment.NewLine+"}");
sb.Append(@"
//新增图片效果
function setTransition()
if (document.all)
document.all('rImage').filters.revealTrans.Transition=Math.floor(Math.random()*23);
document.all('rImage').filters.revealTrans.apply();
function playTransition()
if (document.all)
document.all('rImage').filters.revealTrans.play();
strHtml=@"
function rotateImage()
if (interval == 0)
if (flagtime == 1)
var now = new Date();
var time1 = now.getTime();
var time2;
time1=time1+3000;
time2=now.getTime();
while(time2 & time1)
now = new Date();
time2=now.getTime();
flagtime=0;
var new_image = getNextImage();
setTransition();//新增图片效果
document['rImage'].src = new_
var recur_call = ""rotateImage('""+'rImage'+""')"";
playTransition();//新增图片效果
setTimeout(recur_call,interval);
// End --&
&script type=""text/javascript"" src=""Resource/Script/ygcss.js""&&/script&
sb.Append(strHtml);
strHtml=@"&/HEAD&&body topmargin=""0"" leftmargin=""0""&&form name=""frmDispImage""&";
sb.Append(strHtml);
strHtml=@"
&table bgcolor='#fff2df' border='0' cellpadding='0' cellspacing='0' width='"
+ScrWidth.ToString()+@"'&
&tbody&&tr&
&td align='center' valign='top'&&table border='0' cellpadding='0' cellspacing='0' width='100%'&
&tbody&&tr&
&td height='1'&&/td&
&/tbody&&/table&
&table border='0' cellpadding='0' cellspacing='0' height='"+
PicHeight.ToString()+"' width='"+PicWidth.ToString()+@"' align='center'&
&tbody&&tr&
&td align='center'&&img name=""rImage"" src='"+
ar1[0].ToString()+"' width='"+PicWidth+"' height='"+PicHeight+@"'
onload=""counternum();"" border='0' style='FILTER: revealTrans(duration=3,transition=20)'&&/td&
&/tbody&&/table&
&!--text--&
&table border='0' cellpadding='0' cellspacing='0' width='100%'&
&tbody&&tr&
&td id=""summary"" align='center' height='"+
TextHeight.ToString()+"' style='font-size:"+FontSize.ToString()+"'&"
+strShopName+"-"+strBrandName+"-"+strResoourceCode+"-"+ar2[0].ToString()+@"&/td&
&/tbody&&/table&
&!--/text--&
&!--bottom--&
&table border='0' cellpadding='0' cellspacing='0' width='100%'&
&tbody&&tr&
&td&&img src='Resource/Image/TurnImage/slideshow_bg_3.gif' border='0' height='4' width='"+ScrWidth.ToString()+@"'&&/td&
&td bgcolor='#c9beeb' height='"+ToolBarHeight.ToString()+@"'&
&table border='0' cellpadding='1' cellspacing='0' width='100%'&
&tbody&&tr&
&td class='sbody' align='center' height='"+len3.ToString()+"'&&font style='font-size:"+FontSize+"'&&span id='span_1'&1&/span&/"+len.ToString()+
&img src='Resource/Image/TurnImage/slideshows_bottom_1.gif' alt='开始' onclick=""run(document.frmDispImage.select1)"" align='absmiddle' border='0' height='21' width='35'&&&&img src='Resource/Image/TurnImage/slideshows_bottom_2.gif' alt='停止' onclick=""stop();"" align='absmiddle' border='0' height='21' width='35'&
&&&img src='Resource/Image/TurnImage/slideshows_bottom_3.gif' alt='前一张' onclick=""previous();"" align='absmiddle' border='0' height='21' width='35'&&&&img src='Resource/Image/TurnImage/slideshows_bottom_4.gif' alt='后一张' onclick=""next();"" align='absmiddle' border='0' height='21' width='35'&&&速度：
&select name=""select1"" onchange=""run(this)""&
&option value=""12""&12 sec.&/option&
&option selected=""selected"" value=""6""&6 sec.&/option&
&option value=""3""&3 sec.&/option&
&option value=""9""&9 sec.&/option&
&/select&&/font&&/td&
&/tbody&&/table&
&td bgcolor='#625a68' height='1'&&/td&
&/tbody&&/table&
&/tbody&&/table&
&!--/--&&/center&
&script language=""javascript""&
flagtime=1;
rotateImage();//修改为图片自动播放
&!--slideit()--&
&/center&";
sb.Append(strHtml);
sb.Append("&/form&&/body&&/HTML&");
return sb.ToString();
调用格式：
ArrayList ar1=new ArrayList();
ar1.Clear();
ar1.Add("http://downmoon-hgh/RichChartServer/Top30/frmForumDisplayImage.aspx?id=095407");
ar1.Add("http://downmoon-hgh/RichChartServer/Top30/frmForumDisplayImage.aspx?id=085709");
ar1.Add("http://downmoon-hgh/RichChartServer/Top30/frmForumDisplayImage.aspx?id=091030");
ar1.Add("http://downmoon-hgh/RichChartServer/Top30/frmForumDisplayImage.aspx?id=092445");
drawPic(ar1);
if(this.ar1==null || ar1.Count==0
// this.MsgBox("该席位/资源无外观图！");
// this.CloseWebForm();
drawPic(ar1);
private void drawPic(ArrayList arrImages)
if(pic==null)
pic=new clsTurnPicture();
ArrayList ar2=new ArrayList();
ar2.Clear();
for(int k=0;k&arrImages.Ck++)
ar2.Add("展示图"+i.ToString());
Response.Write(pic.getPicsForHtml(arrImages,ar2,CurrShopName,CurBrandName,strResourceCode,2,640,36,34,16));
SQL中通配符、转义符与"["号的使用(downmoon)
一、搜索通配符字符的说明
可以搜索通配符字符。有两种方法可指定平常用作通配符的字符：
使用 ESCAPE 关键字定义转义符。在模式中，当转义符置于通配符之前时，该通配符就解释为普通字符。例如，要搜索在任意位置包含字符串 5% 的字符串，请使用：
WHERE ColumnA LIKE '%5/%%' ESCAPE '/'
在上述 LIKE 子句中，前导和结尾百分号 (%) 解释为通配符，而斜杠 (/) 之后的百分号解释为字符 %。
在方括号 ([ ]) 中只包含通配符本身。要搜索破折号 (-) 而不是用它指定搜索范围，请将破折号指定为方括号内的第一个字符：
WHERE ColumnA LIKE '9[-]5'
下表显示了括在方括号内的通配符的用法。
LIKE '5[%]' 5%
LIKE '5%' 5 后跟 0 个或更多字符的字符串
LIKE '[_]n' _n
LIKE '_n' an, in, on (and so on)
LIKE '[a-cdf]' a, b, c, d, or f
LIKE '[-acdf]' -, a, c, d, or f
LIKE '[ [ ]' [
LIKE ']' ]
二、实例说明：
在表PersonalMember中查找strloginname字段中含有"["的记录。
可用三条语句：
select strloginname,* from PersonalMember where strloginname like '%[%' escape ''
2、(说明"\"与"/"均可与escape关键字结合作为转义符)
select strloginname,* from PersonalMember where strloginname like '%/[%' escape '/'
select strloginname,* from dbo.PersonalMember where charindex('[',strloginname)&0
select strloginname,* from dbo.PersonalMember where strloginname
SQL语句导入／导出数据集
一.在MSSQL2000中使用Transact-SQL语句处理DBF数据转换
1、查询dbf数据
SELECT * FROM OpenDataSource('vfpoledb','Data Source="d:vfptest.dbc"')...test1
SELECT a.* FROM OPENROWSET('MSDASQL', 'DRIVER={Microsoft Visual FoxPro Driver};
Exclusive=No;Collate=MDeleted=YNull=No; SourceDB=d:vfptest.
SourceType=DBC;', 'SELECT * FROM test1') AS a Select * From Openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro DSourceDB=d:SourceType=DBF',
'select * from test3')
2、将DBF数据导入mssql中
SELECT * into test1 FROM OpenDataSource('vfpoledb','Data Source="d:vfptest.dbc"')...test1
3、将SQL数据导入dbf中
insert into OPENROWSET('MSDASQL', 'DRIVER=Microsoft Visual FoxPro Driver}; Exclusive=No;
Collate=MDeleted=YNull=No; SourceDB=d:vfptest.SourceType=DBC;',
'SELECT * FROM test1')
select * from Test1
4、修改数据
Update openrowset('MSDASQL','Driver=Microsoft Visual FoxPro DSourceDB=d:SourceType=DBF',
'select * from test3 where code="11"') set name = '"12"'
二.导入/导出dBase
/*=================================================*/
--如果接受数据导入的表已经存在
insert into 表 select * from
openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=c:','select * from [test.dbf]')
--如果导入数据并生成表
select * into 表 from
openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=c:','select * from [test.dbf]')
/*===============================================*/
--如果从SQL数据库中,导出数据到dBase,如果dBase文件已经存在,就可以简单的用:
insert into
openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=c:','select * from [test.dbf]')
select * from 表
DATABASE=c: c:是dbf文件的存放目录
'select * from [test.dbf] test.dbf是指dbf文件名
--如果dBase文件不存在,就需要用到下面的存储过程了.
/*--数据导出dBase
导出表中的数据到dBase,如果文件不存在,将自动创建文件
基于通用性考虑,仅支持导出标准数据类型
/*--调用示例
--导出dBase
p_exporttb @tbname='地区资料',@path='c:',@over=0
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_exporttb]
create proc p_exporttb
@tbname sysname, --要导出的表名
@path nvarchar(1000), --文件存放目录
@fname nvarchar(250)='', --文件名,默认为表名
@over bit=0 --是否覆盖已经存在的文件,如果不覆盖,则直接追加
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
--参数检测
if isnull(@fname,'')='' set @fname=@tbname+'.dbf'
--检查文件是否已经存在
if right(@path,1)&&'' set @path=@path+''
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
if exists(select 1 from #tb where a=1)
if @over=1
set @sql='del '+@sql
exec master..xp_cmdshell @sql,no_output
set @over=0
set @over=1
--数据库创建语句
set @sql=@path+@fname
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="dBASE 5.0;'
+';HDR=NO;DATABASE='+@path+'"'
--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err&&0 goto lberr
exec @err=sp_oamethod @obj,'open',null,@constr
if @err&&0 goto lberr
--创建表的SQL
select @sql='',@fdlist=''
select @fdlist=@fdlist+','+a.name
,@sql=@sql+',['+a.name+'] '
+case when b.name in('char','nchar','varchar','nvarchar') then
'text('+cast(case when a.length&250 then 250 else a.length end as varchar)+')'
when b.name in('tynyint','int','bigint','tinyint') then 'int'
when b.name in('smalldatetime','datetime') then 'datetime'
when b.name in('money','smallmoney') then 'money'
else b.name end
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
and object_id(@tbname)=id
select @sql='create table ['+@fname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)
if @over=1
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err&&0 goto lberr
exec @err=sp_oadestroy @obj
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''dBase 5.0;DATABASE='
+@path+''',''select * from ['+@fname+']'')'
--导入数据
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname)
exec sp_oageterrorinfo 0,@src out,@desc out
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist
/*--数据导出dBase
导出查询语句中的数据到dBase,如果文件不存在,将自动创建文件
基于通用性考虑,仅支持导出标准数据类型
/*--调用示例
--导出dBase
p_exporttb @sqlstr='select * from 地区资料',@path='c:',@over=1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_exporttb]
create proc p_exporttb
@sqlstr varchar(8000), --要导出的查询名
@path nvarchar(1000), --文件存放目录
@fname nvarchar(250)='temp.dbf',--文件名,默认为temp
@over bit=0 --是否覆盖已经存在的文件,如果不覆盖,则直接追加
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
--参数检测
if isnull(@fname,'')='' set @fname='temp.dbf'
--检查文件是否已经存在
if right(@path,1)&&'' set @path=@path+''
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
if exists(select 1 from #tb where a=1)
if @over=1
set @sql='del '+@sql
exec master..xp_cmdshell @sql,no_output
set @over=0
set @over=1
--数据库创建语句
set @sql=@path+@fname
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="dBASE 5.0;'
+';HDR=NO;DATABASE='+@path+'"'
--创建表的SQL
declare @tbname sysname
set @tbname='##tmp_'+convert(varchar(38),newid())
set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
exec(@sql)
--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err&&0 goto lberr
exec @err=sp_oamethod @obj,'open',null,@constr
if @err&&0 goto lberr
--创建表的SQL
select @sql='',@fdlist=''
select @fdlist=@fdlist+','+a.name
,@sql=@sql+',['+a.name+'] '
+case when b.name in('char','nchar','varchar','nvarchar') then
'text('+cast(case when a.length&250 then 250 else a.length end as varchar)+')'
when b.name in('tynyint','int','bigint','tinyint') then 'int'
when b.name in('smalldatetime','datetime') then 'datetime'
when b.name in('money','smallmoney') then 'money'
else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
and a.id=(select id from tempdb..sysobjects where name=@tbname)
select @sql='create table ['+@fname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)
if @over=1
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err&&0 goto lberr
exec @err=sp_oadestroy @obj
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''dBase 5.0;DATABASE='
+@path+''',''select * from ['+@fname+']'')'
--导入数据
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')
set @sql='drop table ['+@tbname+']'
exec(@sql)
exec sp_oageterrorinfo 0,@src out,@desc out
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist
三.ＳＱＬ语句导入／导出大全
/******* 导出到excel
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:temp1.xls -c -q
-S"GNETDATA/GNETDATA" -U"sa" -P""'
/*********** 导入Excel
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:test.xls";User ID=APassword=;Extended properties=Excel 5.0')...xactions
/*动态文件名
declare @fn varchar(20),@s varchar(1000)
set @fn = 'c:test.xls'
set @s ='''Microsoft.Jet.OLEDB.4.0'',
''Data Source="'+@fn+'";User ID=APassword=;Extended properties=Excel 5.0'''
set @s = 'SELECT * FROM OpenDataSource ('+@s+')...sheet1$'
SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+'　' 转换后的别名
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:test.xls";User ID=APassword=;Extended properties=Excel 5.0')...xactions
/********************** EXCEL导到远程SQL
insert OPENDATASOURCE(
'SQLOLEDB',
'Data Source=远程User ID=Password=密码'
).库名.dbo.表名 (列名1,列名2)
SELECT 列名1,列名2
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:test.xls";User ID=APassword=;Extended properties=Excel 5.0')...xactions
/** 导入文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename in cT.txt -c -Sservername -Usa -Ppassword'
/** 导出文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename out cT.txt -c -Sservername -Usa -Ppassword'
EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout cT.txt -c
-Sservername -Usa -Ppassword'
导出到TXT文本，用逗号分开
exec master..xp_cmdshell 'bcp "库名..表名" out "d:tt.txt" -c -t ,-U sa -P password'
BULK INSERT 库名..表名
FROM 'c:test.txt'
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 DSourceType=DBF;SourceDB=c:',
'select * from [aa.DBF]')
/**************导入DBF文件****************/
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro D
SourceDB=e:VFP98
SourceType=DBF',
'select * from customer where country != "USA" order by country')
/***************** 导出到DBF ***************/
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句
insert into openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro DSourceType=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 ')
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="f:northwind.mdb";Jet OLEDBatabase Password=123;User ID=APassword=;')...产品
********************* 导入 xml　文件
DECLARE @idoc int
DECLARE @doc varchar(1000)
--sample XML document
SET @doc ='
Customer was very satisfied
white red" /&
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.
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 into
opendatasource('MICROSOFT.JET.OLEDB.4.0'
,'THDR=YDATABASE=C:'
)...[aa#txt]
--,aa#txt)
select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
opendatasource('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:a.xls'
--,Sheet1$)
)...[Sheet1$]
如果你想直接插入并生成文本文件,就要用bcp
declare @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"" /c'
exec 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 File2Table
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为导入
declare @sql varchar(8000)
if @tbname like '%.%.%' --如果指定了表名,则直接导出单个表
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
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)
fetch next from #tb into @m_tbname
while @@fetch_status=0
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
deallocate #tb
/************* Oracle **************/
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
delete 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=888
insert into openquery(mailser,'select disorder,catago from yulin')values(333,777)
对于用bcp导出,是没有字段名的.
用openrowset导出,需要事先建好表.
用openrowset导入,除ACCESS及EXCEL外,均不支持非本机数据导入
create proc up_010
declare @sql varchar(99),@tbl varchar(30),@fk varchar(30)
declare cur_fk cursor local for
select object_name(constid),object_name(fkeyid) from sysreferences
--删除所有外键
open cur_fk
fetch cur_fk into @fk,@tbl
while @@fetch_status =0
select @sql='alter table '+@tbl+' nocheck constraint '+@fk
exec(@SQL)
select @sql='delete from '+@tbl
exec(@SQL)
fetch cur_fk into @fk,@tbl
close cur_fk
declare cur_fks cursor local for
select name from sysobjects where xtype='U'
open cur_fks
fetch cur_fks into @tbl
while @@fetch_status =0
select @sql='delete from ['+@tbl+']'
exec(@SQL)
fetch cur_fks into @tbl
close cur_fks
exec up_010
MSSQL生成整个数据库的SQL脚本的工具
scptxfr.exe的路径要正确
declare @cMd varchar(1000)
set @cmd = 'master.dbo.xp_cmdshell ' +
'''c:\"Microsoft ' +
'SQL Server"' +
'\MSSQL\Upgrade\scptxfr.exe ' +
' /s YourServerName /p YourSAPassword /I /d YourDBName /f ' +
'c:\YourDBName.sql'''
exec (@cmd)
工具参数说明：
SCPTXFR /s &server& /d &database& {[/I] | [/P &password&]}
{[/F &script files directory&] | [/f &single script file&]}
/q /r /O /T /A /E /C &CodePage& /N /X /H /G /Y /?
/s - Indicates the source server to connect to.
/d - Indicates the source database to script.
/I - Use integrated security.
/P - Password to use for 'sa'. Note that login ID is always 'sa'.
If /P not used or if a password does not follow the flag,
a null password is used. Not compatible with /I.
/F - The directory into which the script files should be generated.
This means one file is generated for each category of objects.
/f - The single file in which all script is to be saved.
Not compatible with /F.
/q - Use quoted identifiers in the generated scripts.
/r - Include drop statements for the objects in the script.
/O - Generate OEM script files. Cannot be used with /A or /T.
This is the default behavior.
/T - Generate UNICODE script files. Cannot be used with /A or /O.
/A - Generate ANSI script files. Cannot be used with /T or /O.
/? - Command line help.
/E - Stop scripting when error occurs.
Default behavior is to log the error, and continue.
/C - Indicate the CodePage which overrides the server CodePage.
/N - Generate ANSI PADDING.
/X - Script SPs and XPs to separate files.
/H - Generate script files without header (default: with header).
/G - Use the specified server name as the prefix for the generated
output files(to handle dashes in server name).
/Y - Generate script for Extended Properties (valid for 8.x server
// 保存可執行文件到COMMON數據庫
long ll_filenum, ll_len, ll_ret
blob lblb_c, lblb_total
boolean lb_auto
lb_auto = gnv_app.inv_sec.autocommit
gnv_app.inv_sec.autocommit = true
If not fileexists(as_file) Then RETURN FAILURE
ll_filenum = fileopen(as_file, StreamMode!, Read!, Shared!)
ll_len = fileread(ll_filenum, lblb_c)
do while ll_len & 0
lblb_total = lblb_total + lblb_c
ll_len = fileread(ll_filenum, lblb_c)
fileclose(ll_filenum)
updateblob app_files set content = :lblb_total where id = :al_id using gnv_app.inv_
ll_ret = gnv_app.inv_sec.SQLNRows
gnv_app.inv_sec.autocommit = lb_auto
If ll_Ret &= 0 Then RETURN FAILURE
RETURN SUCCESS
Dlg_SetupDatabase://安装数据库的界面
szMsg="请选择应用安装类型";
szTitle="应用程序安装类型";
SetupDatabase="服务器端";
noSetupDatabase="客户端";
svCheck1=TRUE;
svCheck2=FALSE;
nResult=AskOptions(EXCLUSIVE,szMsg,SetupDatabase,svCheck1,noSetupDatabase,svCheck2);
if nResult=BACK goto Dlg_SdRegisterUserEx;
if svCheck1=TRUE then
goto Dlg_SdShowDlgEdit3;
//modify by sds
//if svCheck2=TRUE goto Dlg_SdAskDestP
if svCheck2=TRUE goto Dlg_SdShowDlgEdit3;
Dlg_SdShowDlgEdit3:
szTitle="请输入数据库信息";
szMsg="请输入数据库信息";
ServerName="服务器名称";
UserName="用户名称";
Password="数据库口令";
svEdit1="";
svEdit2="sa";
svEdit3="";
nResult=SdShowDlgEdit3(szTitle,szMsg,ServerName,UserName,Password,svEdit1,svEdit2,svEdit3);
//这个地方要产生一个脚本文件CreateDatabase.S
//if (nResult=BACK) goto Dlg_SetupD
if (nResult=BACK) then
goto Dlg_SetupD
WriteProfString (WINSYSDIR^"DbInfo.ini", "DatabaseInfo", "ServerName",svEdit1);
WriteProfString (WINSYSDIR^"DbInfo.ini", "DatabaseInfo", "UserName",svEdit2);
WriteProfString (WINSYSDIR^"DbInfo.ini", "DatabaseInfo", "PassWord",svEdit3);
if (svCheck2=TRUE) goto Dlg_SdAskDestP
if svCheck1=TRUE then
OpenFileMode(FILE_MODE_APPEND);
CreateFile(DataBaseHandle,WINSYSDIR,"CheckDataBaseInfo.Bat");
//dbCheck.Sql－－应该是我们自己带的SRCDIR^
CheckDataBaseJbStr="@isqlw -S %s -d master -U %s -P %s -i "+SRCDIR^"dbCheck.sql
&&&&推荐文章:
【上篇】【下篇】