MS SQL Server将数据导出Insert语句的几种方案

<p>少量数据直接导出成SQL语句,使用起来比较方便。大量数据,就用SQL Server的导入导出功能了。</p>
<p>使用过几种方式(仍然是用Google去搜索……):</p>
<p>1.<strong>直接使用存储过程</strong>
</p>
<p>SQL Server 2000使用存储过程导出insert语句</p>
<p>今天使用了一个通过存储过程生成sqlserver数据库insert语句的存储过程,我觉得很好用,谢谢这位高手了</p>
<div class="dp-highlighter">
<div class="bar">
<div class="tools">Java代码 <a title="复制代码" href="http://wangchao-17915566.javaeye.com/blog/175361#"><img src="http://wangchao-17915566.javaeye.com/images/icon_copy.gif" alt="复制代码"></a>
</div>
</div>
<ol class="dp-j">
<li>
<span><span class="keyword"><strong><span style="color: #7f0055;">if</span>
</strong>
</span>
<span>exists(select*fromsysobjectswheretype=</span>
<span class="string"><span style="color: #0000ff;">'p'</span>
</span>
<span>andname=</span>
<span class="string"><span style="color: #0000ff;">'UspOutputData'</span>
</span>
<span>) </span>
</span>
</li>
<li>
<span>dropprocUspOutputData </span>
</li>
<li>
<span>GO </span>
</li>
<li>
<span>CREATEPROCEDUREdbo.UspOutputData </span>
</li>
<li>
<span class="annotation"><span style="color: #646464;">@tablename</span>
</span>
<span>sysname </span>
</li>
<li>
<span>AS </span>
</li>
<li>
<span>declare</span>
<span class="annotation"><span style="color: #646464;">@column</span>
</span>
<span>varchar(</span>
<span class="number"><span style="color: #c00000;">2000</span>
</span>
<span>) </span>
</li>
<li>
<span>declare</span>
<span class="annotation"><span style="color: #646464;">@columndata</span>
</span>
<span>varchar(</span>
<span class="number"><span style="color: #c00000;">2000</span>
</span>
<span>) </span>
</li>
<li>
<span>declare</span>
<span class="annotation"><span style="color: #646464;">@sql</span>
</span>
<span>varchar(</span>
<span class="number"><span style="color: #c00000;">8000</span>
</span>
<span>) </span>
</li>
<li>
<span>declare</span>
<span class="annotation"><span style="color: #646464;">@xtype</span>
</span>
<span>tinyint </span>
</li>
<li>
<span>declare</span>
<span class="annotation"><span style="color: #646464;">@name</span>
</span>
<span>sysname </span>
</li>
<li>
<span>declare</span>
<span class="annotation"><span style="color: #646464;">@objectId</span>
</span>
<span></span>
<span class="keyword"><strong><span style="color: #7f0055;">int</span>
</strong>
</span>
<span> </span>
</li>
<li>
<span>declare</span>
<span class="annotation"><span style="color: #646464;">@objectname</span>
</span>
<span>sysname </span>
</li>
<li>
<span>declare</span>
<span class="annotation"><span style="color: #646464;">@ident</span>
</span>
<span></span>
<span class="keyword"><strong><span style="color: #7f0055;">int</span>
</strong>
</span>
<span> </span>
</li>
<li>
<span>setnocounton </span>
</li>
<li>
<span>--判斷對象是否存在 </span>
</li>
<li>
<span>set</span>
<span class="annotation"><span style="color: #646464;">@objectId</span>
</span>
<span>=object_id(</span>
<span class="annotation"><span style="color: #646464;">@tablename</span>
</span>
<span>) </span>
</li>
<li>
<span class="keyword"><strong><span style="color: #7f0055;">if</span>
</strong>
</span>
<span></span>
<span class="annotation"><span style="color: #646464;">@objectId</span>
</span>
<span>is</span>
<span class="keyword"><strong><span style="color: #7f0055;">null</span>
</strong>
</span>
<span> </span>
</li>
<li>
<span>begin </span>
</li>
<li>
<span>print</span>
<span class="string"><span style="color: #0000ff;">'Theobjectnotexists'</span>
</span>
<span> </span>
</li>
<li>
<span class="keyword"><strong><span style="color: #7f0055;">return</span>
</strong>
</span>
<span> </span>
</li>
<li>
<span>end </span>
</li>
<li>
<span>--此判断不严密 </span>
</li>
<li>
<span>set</span>
<span class="annotation"><span style="color: #646464;">@objectname</span>
</span>
<span>=rtrim(object_name(</span>
<span class="annotation"><span style="color: #646464;">@objectId</span>
</span>
<span>)) </span>
</li>
<li>
<span class="keyword"><strong><span style="color: #7f0055;">if</span>
</strong>
</span>
<span></span>
<span class="annotation"><span style="color: #646464;">@objectname</span>
</span>
<span>is</span>
<span class="keyword"><strong><span style="color: #7f0055;">null</span>
</strong>
</span>
<span>orcharindex(</span>
<span class="annotation"><span style="color: #646464;">@objectname</span>
</span>
<span>,</span>
<span class="annotation"><span style="color: #646464;">@tablename</span>
</span>
<span>)=</span>
<span class="number"><span style="color: #c00000;">0</span>
</span>
<span> </span>
</li>
<li>
<span>begin </span>
</li>
<li>
<span>print</span>
<span class="string"><span style="color: #0000ff;">'objectnotincurrentdatabase'</span>
</span>
<span> </span>
</li>
<li>
<span class="keyword"><strong><span style="color: #7f0055;">return</span>
</strong>
</span>
<span> </span>
</li>
<li>
<span>end </span>
</li>
<li>
<span>--判斷對象是否是table </span>
</li>
<li>
<span class="keyword"><strong><span style="color: #7f0055;">if</span>
</strong>
</span>
<span>OBJECTPROPERTY(</span>
<span class="annotation"><span style="color: #646464;">@objectId</span>
</span>
<span>,</span>
<span class="string"><span style="color: #0000ff;">'IsTable'</span>
</span>
<span>)<></span>
<span class="number"><span style="color: #c00000;">1</span>
</span>
<span> </span>
</li>
<li>
<span>begin </span>
</li>
<li>
<span>print</span>
<span class="string"><span style="color: #0000ff;">'Theobjectisnottable'</span>
</span>
<span> </span>
</li>
<li>
<span class="keyword"><strong><span style="color: #7f0055;">return</span>
</strong>
</span>
<span> </span>
</li>
<li>
<span>end </span>
</li>
<li>
<span>--不知道打印的意义 </span>
</li>
<li>
<span>select</span>
<span class="annotation"><span style="color: #646464;">@ident</span>
</span>
<span>=status&</span>
<span class="number"><span style="color: #c00000;">0x80</span>
</span>
<span>fromsyscolumnswhereid=</span>
<span class="annotation"><span style="color: #646464;">@objectid</span>
</span>
<span>andstatus&</span>
<span class="number"><span style="color: #c00000;">0x80</span>
</span>
<span>=</span>
<span class="number"><span style="color: #c00000;">0x80</span>
</span>
<span> </span>
</li>
<li>
<span class="keyword"><strong><span style="color: #7f0055;">if</span>
</strong>
</span>
<span></span>
<span class="annotation"><span style="color: #646464;">@ident</span>
</span>
<span>isnot</span>
<span class="keyword"><strong><span style="color: #7f0055;">null</span>
</strong>
</span>
<span> </span>
</li>
<li>
<span>print</span>
<span class="string"><span style="color: #0000ff;">'SETIDENTITY_INSERT'</span>
</span>
<span>+</span>
<span class="annotation"><span style="color: #646464;">@TableName</span>
</span>
<span>+</span>
<span class="string"><span style="color: #0000ff;">'ON'</span>
</span>
<span> </span>
</li>
<li>
<span></span>
</li>
<li>
<span>declaresyscolumns_cursorcursor </span>
</li>
<li>
<span class="keyword"><strong><span style="color: #7f0055;">for</span>
</strong>
</span>
<span>selectc.name,c.xtypefromsyscolumnscwherec.id=</span>
<span class="annotation"><span style="color: #646464;">@objectid</span>
</span>
<span>orderbyc.colid </span>
</li>
<li>
<span>opensyscolumns_cursor </span>
</li>
<li>
<span>set</span>
<span class="annotation"><span style="color: #646464;">@column</span>
</span>
<span>=</span>
<span class="string"><span style="color: #0000ff;">''</span>
</span>
<span> </span>
</li>
<li>
<span>set</span>
<span class="annotation"><span style="color: #646464;">@columndata</span>
</span>
<span>=</span>
<span class="string"><span style="color: #0000ff;">''</span>
</span>
<span> </span>
</li>
<li>
<span>fetchnextfromsyscolumns_cursorinto</span>
<span class="annotation"><span style="color: #646464;">@name</span>
</span>
<span>,</span>
<span class="annotation"><span style="color: #646464;">@xtype</span>
</span>
<span> </span>
</li>
<li>
<span class="keyword"><strong><span style="color: #7f0055;">while</span>
</strong>
</span>
<span>@</span>
<span class="annotation"><span style="color: #646464;">@fetch_status</span>
</span>
<span><>-</span>
<span class="number"><span style="color: #c00000;">1</span>
</span>
<span> </span>
</li>
<li>
<span>begin </span>
</li>
<li>
<span></span>
<span class="keyword"><strong><span style="color: #7f0055;">if</span>
</strong>
</span>
<span>@</span>
<span class="annotation"><span style="color: #646464;">@fetch_status</span>
</span>
<span><>-</span>
<span class="number"><span style="color: #c00000;">2</span>
</span>
<span> </span>
</li>
<li>
<span>begin </span>
</li>
<li>
<span>--</span>
<span class="keyword"><strong><span style="color: #7f0055;">if</span>
</strong>
</span>
<span></span>
<span class="annotation"><span style="color: #646464;">@xtype</span>
</span>
<span>notin(</span>
<span class="number"><span style="color: #c00000;">189</span>
</span>
<span>,</span>
<span class="number"><span style="color: #c00000;">34</span>
</span>
<span>,</span>
<span class="number"><span style="color: #c00000;">35</span>
</span>
<span>,</span>
<span class="number"><span style="color: #c00000;">99</span>
</span>
<span>,</span>
<span class="number"><span style="color: #c00000;">98</span>
</span>
<span>)--timestamp不需处理,image,text,ntext,sql_variant暂时不处理 </span>
</li>
<li>
<span>begin </span>
</li>
<li>
<span>set</span>
<span class="annotation"><span style="color: #646464;">@column</span>
</span>
<span>=</span>
<span class="annotation"><span style="color: #646464;">@column</span>
</span>
<span>+</span>
<span class="keyword"><strong><span style="color: #7f0055;">case</span>
</strong>
</span>
<span>whenlen(</span>
<span class="annotation"><span style="color: #646464;">@column</span>
</span>
<span>)=</span>
<span class="number"><span style="color: #c00000;">0</span>
</span>
<span>then</span>
<span class="string"><span style="color: #0000ff;">''</span>
</span>
<span></span>
<span class="keyword"><strong><span style="color: #7f0055;">else</span>
</strong>
</span>
<span></span>
<span class="string"><span style="color: #0000ff;">','</span>
</span>
<span>end+</span>
<span class="annotation"><span style="color: #646464;">@name</span>
</span>
<span> </span>
</li>
<li>
<span>set</span>
<span class="annotation"><span style="color: #646464;">@columndata</span>
</span>
<span>=</span>
<span class="annotation"><span style="color: #646464;">@columndata</span>
</span>
<span>+</span>
<span class="keyword"><strong><span style="color: #7f0055;">case</span>
</strong>
</span>
<span>whenlen(</span>
<span class="annotation"><span style="color: #646464;">@columndata</span>
</span>
<span>)=</span>
<span class="number"><span style="color: #c00000;">0</span>
</span>
<span>then</span>
<span class="string"><span style="color: #0000ff;">''</span>
</span>
<span></span>
<span class="keyword"><strong><span style="color: #7f0055;">else</span>
</strong>
</span>
<span></span>
<span style="color: #0000ff;"><span class="string">','</span>
<span class="string">','</span>
<span class="string">','</span>
</span>
<span>end </span>
</li>
<li>
<span>+</span>
<span class="keyword"><strong><span style="color: #7f0055;">case</span>
</strong>
</span>
<span>when</span>
<span class="annotation"><span style="color: #646464;">@xtype</span>
</span>
<span>in(</span>
<span class="number"><span style="color: #c00000;">167</span>
</span>
<span>,</span>
<span class="number"><span style="color: #c00000;">175</span>
</span>
<span>)then</span>
<span style="color: #0000ff;"><span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">'+'</span>
</span>
<span>+</span>
<span class="annotation"><span style="color: #646464;">@name</span>
</span>
<span>+</span>
<span style="color: #0000ff;"><span class="string">'+'</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
</span>
<span>--varchar,</span>
<span class="keyword"><strong><span style="color: #7f0055;">char</span>
</strong>
</span>
<span> </span>
</li>
<li>
<span>when</span>
<span class="annotation"><span style="color: #646464;">@xtype</span>
</span>
<span>in(</span>
<span class="number"><span style="color: #c00000;">231</span>
</span>
<span>,</span>
<span class="number"><span style="color: #c00000;">239</span>
</span>
<span>)then</span>
<span style="color: #0000ff;"><span class="string">''</span>
<span class="string">'N'</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">'+'</span>
</span>
<span>+</span>
<span class="annotation"><span style="color: #646464;">@name</span>
</span>
<span>+</span>
<span style="color: #0000ff;"><span class="string">'+'</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
</span>
<span>--nvarchar,nchar </span>
</li>
<li>
<span>when</span>
<span class="annotation"><span style="color: #646464;">@xtype</span>
</span>
<span>=</span>
<span class="number"><span style="color: #c00000;">61</span>
</span>
<span>then</span>
<span style="color: #0000ff;"><span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">'+convert(char(23),'</span>
</span>
<span>+</span>
<span class="annotation"><span style="color: #646464;">@name</span>
</span>
<span>+</span>
<span style="color: #0000ff;"><span class="string">',121)+'</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
</span>
<span>--datetime </span>
</li>
<li>
<span>when</span>
<span class="annotation"><span style="color: #646464;">@xtype</span>
</span>
<span>=</span>
<span class="number"><span style="color: #c00000;">58</span>
</span>
<span>then</span>
<span style="color: #0000ff;"><span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">'+convert(char(16),'</span>
</span>
<span>+</span>
<span class="annotation"><span style="color: #646464;">@name</span>
</span>
<span>+</span>
<span style="color: #0000ff;"><span class="string">',120)+'</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
</span>
<span>--smalldatetime </span>
</li>
<li>
<span>when</span>
<span class="annotation"><span style="color: #646464;">@xtype</span>
</span>
<span>=</span>
<span class="number"><span style="color: #c00000;">36</span>
</span>
<span>then</span>
<span style="color: #0000ff;"><span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">'+convert(char(36),'</span>
</span>
<span>+</span>
<span class="annotation"><span style="color: #646464;">@name</span>
</span>
<span>+</span>
<span style="color: #0000ff;"><span class="string">')+'</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
<span class="string">''</span>
</span>
<span>--uniqueidentifier </span>
</li>
<li>
<span></span>
<span class="keyword"><strong><span style="color: #7f0055;">else</span>
</strong>
</span>
<span></span>
<span class="annotation"><span style="color: #646464;">@name</span>
</span>
<span>end </span>
</li>
<li>
<span>end </span>
</li>
<li>
<span>end </span>
</li>
<li>
<span>fetchnextfromsyscolumns_cursorinto</span>
<span class="annotation"><span style="color: #646464;">@name</span>
</span>
<span>,</span>
<span class="annotation"><span style="color: #646464;">@xtype</span>
</span>
<span> </span>
</li>
<li>
<span>end </span>
</li>
<li>
<span>closesyscolumns_cursor </span>
</li>
<li>
<span>deallocatesyscolumns_cursor </span>
</li>
<li>
<span>set</span>
<span class="annotation"><span style="color: #646464;">@sql</span>
</span>
<span>=</span>
<span style="color: #0000ff;"><span class="string">'setnocountonselect'</span>
<span class="string">'insert'</span>
</span>
<span>+</span>
<span class="annotation"><span style="color: #646464;">@tablename</span>
</span>
<span>+</span>
<span class="string"><span style="color: #0000ff;">'('</span>
</span>
<span>+</span>
<span class="annotation"><span style="color: #646464;">@column</span>
</span>
<span>+</span>
<span style="color: #0000ff;"><span class="string">')values('</span>
<span class="string">'as'</span>
<span class="string">'--'</span>
<span class="string">','</span>
</span>
<span>+</span>
<span class="annotation"><span style="color: #646464;">@columndata</span>
</span>
<span>+</span>
<span style="color: #0000ff;"><span class="string">','</span>
<span class="string">')'</span>
<span class="string">'from'</span>
</span>
<span>+</span>
<span class="annotation"><span style="color: #646464;">@tablename</span>
</span>
<span> </span>
</li>
<li>
<span>print</span>
<span class="string"><span style="color: #0000ff;">'--'</span>
</span>
<span>+</span>
<span class="annotation"><span style="color: #646464;">@sql</span>
</span>
<span> </span>
</li>
<li>
<span>exec(</span>
<span class="annotation"><span style="color: #646464;">@sql</span>
</span>
<span>) </span>
</li>
<li>
<span>--不知道打印的意义 </span>
</li>
<li>
<span class="keyword"><strong><span style="color: #7f0055;">if</span>
</strong>
</span>
<span></span>
<span class="annotation"><span style="color: #646464;">@ident</span>
</span>
<span>isnot</span>
<span class="keyword"><strong><span style="color: #7f0055;">null</span>
</strong>
</span>
<span> </span>
</li>
<li>
<span>print</span>
<span class="string"><span style="color: #0000ff;">'SETIDENTITY_INSERT'</span>
</span>
<span>+</span>
<span class="annotation"><span style="color: #646464;">@TableName</span>
</span>
<span>+</span>
<span class="string"><span style="color: #0000ff;">'OFF'</span>
</span>
<span> </span>
</li>
<li>
<span>GO </span>
</li>
<li>
<span></span>
</li>
<li>
<span>execUspOutputDataT_Sys_Dict_Data</span>
</li>
</ol>
</div>
<p>觉得这个不错,最后使用exec UspOutputData T_Sys_Dict_Data 执行存储过程就ok了。 T_Sys_Dict_Data 是表名,任何表都可以。谢谢! </p>
<p>这种方式看起来比较简单,而且不用安装软件。不过作者没有写支持text/ntext的代码。</p>
<p>2.<strong>使用Codematic代码生成工具</strong>
</p>
<p>使用动软的Codematic代码生成工具,里面有生成SQL方面的功能</p>
<p><a href="http://www.maticsoft.com/">http://www.maticsoft.com/</a>
</p>
<p>codematic是免费软件,好处是成批生成的,最后生成一个文件,但使用中发现有两个BUG:</p>
<p>A.如果内容中有单引号,不会被转换为两个单引号,所以可能出错(.NET1.1版有2.0版都有这个问题)</p>
<p>B.如果数据中有为空字符的内容(估计为NULL的也会),就会把这一列省略掉生成在INSERT语句中,</p>
<p>这个看似智能的功能,在某例本来不允许为NULL,但填写的空字符就出错了。</p>
<p><strong>3.使用MyGeneration</strong>
</p>
<p>Create Tables and Build inserts from Tables by using Mygeneration Templates(Sql Server)()</p>
<p><a href="http://hardrock.cnblogs.com/archive/2005/12/16/298386.html">http://hardrock.cnblogs.com/archive/2005/12/16/298386.html </a>
</p>
<p>MyGeneration是免费软件,我用的是1.3,据说功能强大,不过我没怎么用得上。</p>
<p>速度非常快,而且生成的代码也没有Codematic里提到的问题,生成结果是每个表一个SQL脚本文件,而且还添加了事务处理代码,</p>
<p>看来考虑得非常周到。</p>
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值