当前位置：Gxlcms > 数据库问题 > 使用SQL语句创建SQL数据脚本（应对万网主机部分不支持导出备份数据）

使用SQL语句创建SQL数据脚本（应对万网主机部分不支持导出备份数据）

时间：2021-07-01 10:21:17 帮助过：3人阅读

中的数据。 
  1. <span style="color: #0000ff;">SELECT</span>  <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> <span style="color: #ff0000;">[DB_Temp</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Ad</span><span style="color: #ff0000;">]<br></span>
技术分享 2、编写存储过程。 技术分享 
  1. <span style="color: #008080;"> 1</span> <span style="color: #008080;">--</span><span style="color: #008080;">将表数据生成SQL脚本的存储过程 </span>
  2. <span style="color: #008080;"> 2</span> 
  3. <span style="color: #008080;"> 3</span> <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">PROCEDURE</span><span style="color: #000000;"> dbo.UspOutputData 
  4. </span><span style="color: #008080;"> 4</span> <span style="color: #008000;">@tablename</span><span style="color: #000000;"> sysname 
  5. </span><span style="color: #008080;"> 5</span> <span style="color: #0000ff;">AS</span> 
  6. <span style="color: #008080;"> 6</span> <span style="color: #0000ff;">declare</span> <span style="color: #008000;">@column</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">1000</span><span style="color: #000000;">) 
  7. </span><span style="color: #008080;"> 7</span> <span style="color: #0000ff;">declare</span> <span style="color: #008000;">@columndata</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">1000</span><span style="color: #000000;">) 
  8. </span><span style="color: #008080;"> 8</span> <span style="color: #0000ff;">declare</span> <span style="color: #008000;">@sql</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">4000</span><span style="color: #000000;">) 
  9. </span><span style="color: #008080;"> 9</span> <span style="color: #0000ff;">declare</span> <span style="color: #008000;">@xtype</span> <span style="color: #0000ff;">tinyint</span> 
  10. <span style="color: #008080;">10</span> <span style="color: #0000ff;">declare</span> <span style="color: #008000;">@name</span><span style="color: #000000;"> sysname 
  11. </span><span style="color: #008080;">11</span> <span style="color: #0000ff;">declare</span> <span style="color: #008000;">@objectId</span> <span style="color: #0000ff;">int</span> 
  12. <span style="color: #008080;">12</span> <span style="color: #0000ff;">declare</span> <span style="color: #008000;">@objectname</span><span style="color: #000000;"> sysname 
  13. </span><span style="color: #008080;">13</span> <span style="color: #0000ff;">declare</span> <span style="color: #008000;">@ident</span> <span style="color: #0000ff;">int</span> 
  14. <span style="color: #008080;">14</span> 
  15. <span style="color: #008080;">15</span> <span style="color: #0000ff;">set</span> nocount <span style="color: #0000ff;">on</span> 
  16. <span style="color: #008080;">16</span> <span style="color: #0000ff;">set</span> <span style="color: #008000;">@objectId</span><span style="color: #808080;">=</span><span style="color: #ff00ff;">object_id</span>(<span style="color: #008000;">@tablename</span><span style="color: #000000;">) 
  17. </span><span style="color: #008080;">17</span> 
  18. <span style="color: #008080;">18</span> <span style="color: #0000ff;">if</span> <span style="color: #008000;">@objectId</span> <span style="color: #0000ff;">is</span> <span style="color: #0000ff;">null</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 判断对象是否存在 </span>
  19. <span style="color: #008080;">19</span> <span style="color: #0000ff;">begin</span> 
  20. <span style="color: #008080;">20</span> <span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">The object not exists</span><span style="color: #ff0000;">‘</span> 
  21. <span style="color: #008080;">21</span> <span style="color: #0000ff;">return</span> 
  22. <span style="color: #008080;">22</span> <span style="color: #0000ff;">end</span> 
  23. <span style="color: #008080;">23</span> <span style="color: #0000ff;">set</span> <span style="color: #008000;">@objectname</span><span style="color: #808080;">=</span><span style="color: #ff00ff;">rtrim</span>(<span style="color: #ff00ff;">object_name</span>(<span style="color: #008000;">@objectId</span><span style="color: #000000;">)) 
  24. </span><span style="color: #008080;">24</span> 
  25. <span style="color: #008080;">25</span> <span style="color: #0000ff;">if</span> <span style="color: #008000;">@objectname</span> <span style="color: #0000ff;">is</span> <span style="color: #0000ff;">null</span> <span style="color: #808080;">or</span> <span style="color: #ff00ff;">charindex</span>(<span style="color: #008000;">@objectname</span>,<span style="color: #008000;">@tablename</span>)<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">0</span> <span style="color: #008080;">--</span><span style="color: #008080;">此判断不严密 </span>
  26. <span style="color: #008080;">26</span> <span style="color: #0000ff;">begin</span> 
  27. <span style="color: #008080;">27</span> <span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">object not in current database</span><span style="color: #ff0000;">‘</span> 
  28. <span style="color: #008080;">28</span> <span style="color: #0000ff;">return</span> 
  29. <span style="color: #008080;">29</span> <span style="color: #0000ff;">end</span> 
  30. <span style="color: #008080;">30</span> 
  31. <span style="color: #008080;">31</span> <span style="color: #0000ff;">if</span> <span style="color: #ff00ff;">OBJECTPROPERTY</span>(<span style="color: #008000;">@objectId</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">IsTable</span><span style="color: #ff0000;">‘</span>) <span style="color: #808080;"><</span> <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 判断对象是否是table </span>
  32. <span style="color: #008080;">32</span> <span style="color: #0000ff;">begin</span> 
  33. <span style="color: #008080;">33</span> <span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">The object is not table</span><span style="color: #ff0000;">‘</span> 
  34. <span style="color: #008080;">34</span> <span style="color: #0000ff;">return</span> 
  35. <span style="color: #008080;">35</span> <span style="color: #0000ff;">end</span> 
  36. <span style="color: #008080;">36</span> 
  37. <span style="color: #008080;">37</span> <span style="color: #0000ff;">select</span> <span style="color: #008000;">@ident</span><span style="color: #808080;">=</span>status<span style="color: #808080;">&</span><span style="color: #800000; font-weight: bold;">0x80</span> <span style="color: #0000ff;">from</span> syscolumns <span style="color: #0000ff;">where</span> id<span style="color: #808080;">=</span><span style="color: #008000;">@objectid</span> <span style="color: #808080;">and</span> status<span style="color: #808080;">&</span><span style="color: #800000; font-weight: bold;">0x80</span><span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">0x80</span> 
  38. <span style="color: #008080;">38</span> 
  39. <span style="color: #008080;">39</span> <span style="color: #0000ff;">if</span> <span style="color: #008000;">@ident</span> <span style="color: #0000ff;">is</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span> 
  40. <span style="color: #008080;">40</span> <span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">SET IDENTITY_INSERT </span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span><span style="color: #008000;">@TableName</span><span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;"> ON</span><span style="color: #ff0000;">‘</span> 
  41. <span style="color: #008080;">41</span> 
  42. <span style="color: #008080;">42</span> <span style="color: #0000ff;">declare</span> syscolumns_cursor <span style="color: #0000ff;">cursor</span> 
  43. <span style="color: #008080;">43</span> 
  44. <span style="color: #008080;">44</span> <span style="color: #0000ff;">for</span> <span style="color: #0000ff;">select</span> c.name,c.xtype <span style="color: #0000ff;">from</span> syscolumns c <span style="color: #0000ff;">where</span> c.id<span style="color: #808080;">=</span><span style="color: #008000;">@objectid</span> <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span><span style="color: #000000;"> c.colid 
  45. </span><span style="color: #008080;">45</span> 
  46. <span style="color: #008080;">46</span> <span style="color: #0000ff;">open</span><span style="color: #000000;"> syscolumns_cursor 
  47. </span><span style="color: #008080;">47</span> <span style="color: #0000ff;">set</span> <span style="color: #008000;">@column</span><span style="color: #808080;">=</span><span style="color: #ff0000;">‘‘</span> 
  48. <span style="color: #008080;">48</span> <span style="color: #0000ff;">set</span> <span style="color: #008000;">@columndata</span><span style="color: #808080;">=</span><span style="color: #ff0000;">‘‘</span> 
  49. <span style="color: #008080;">49</span> <span style="color: #0000ff;">fetch</span> <span style="color: #0000ff;">next</span> <span style="color: #0000ff;">from</span> syscolumns_cursor <span style="color: #0000ff;">into</span> <span style="color: #008000;">@name</span>,<span style="color: #008000;">@xtype</span> 
  50. <span style="color: #008080;">50</span> 
  51. <span style="color: #008080;">51</span> <span style="color: #0000ff;">while</span> <span style="color: #008000; font-weight: bold;">@@fetch_status</span> <span style="color: #808080;"><</span> <span style="color: #808080;">>-</span><span style="color: #800000; font-weight: bold;">1</span> 
  52. <span style="color: #008080;">52</span> <span style="color: #0000ff;">begin</span> 
  53. <span style="color: #008080;">53</span> <span style="color: #0000ff;">if</span> <span style="color: #008000; font-weight: bold;">@@fetch_status</span> <span style="color: #808080;"><</span> <span style="color: #808080;">>-</span><span style="color: #800000; font-weight: bold;">2</span> 
  54. <span style="color: #008080;">54</span> <span style="color: #0000ff;">begin</span> 
  55. <span style="color: #008080;">55</span> <span style="color: #0000ff;">if</span> <span style="color: #008000;">@xtype</span> <span style="color: #808080;">not</span> <span style="color: #808080;">in</span>(<span style="color: #800000; font-weight: bold;">189</span>,<span style="color: #800000; font-weight: bold;">34</span>,<span style="color: #800000; font-weight: bold;">35</span>,<span style="color: #800000; font-weight: bold;">99</span>,<span style="color: #800000; font-weight: bold;">98</span>) <span style="color: #008080;">--</span><span style="color: #008080;">timestamp不需处理，image,text,ntext,sql_variant 暂时不处理 </span>
  56. <span style="color: #008080;">56</span> 
  57. <span style="color: #008080;">57</span> <span style="color: #0000ff;">begin</span> 
  58. <span style="color: #008080;">58</span> <span style="color: #0000ff;">set</span> <span style="color: #008000;">@column</span><span style="color: #808080;">=</span><span style="color: #008000;">@column</span><span style="color: #808080;">+</span><span style="color: #ff00ff;">case</span> <span style="color: #0000ff;">when</span> <span style="color: #ff00ff;">len</span>(<span style="color: #008000;">@column</span>)<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">then</span><span style="color: #ff0000;">‘‘</span> <span style="color: #0000ff;">else</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span><span style="color: #0000ff;">end</span><span style="color: #808080;">+</span><span style="color: #008000;">@name</span> 
  59. <span style="color: #008080;">59</span> 
  60. <span style="color: #008080;">60</span> <span style="color: #0000ff;">set</span> <span style="color: #008000;">@columndata</span><span style="color: #808080;">=</span><span style="color: #008000;">@columndata</span><span style="color: #808080;">+</span><span style="color: #ff00ff;">case</span> <span style="color: #0000ff;">when</span> <span style="color: #ff00ff;">len</span>(<span style="color: #008000;">@columndata</span>)<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">then</span> <span style="color: #ff0000;">‘‘</span> <span style="color: #0000ff;">else</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> 
  61. <span style="color: #008080;">61</span> <span style="color: #0000ff;">end</span> 
  62. <span style="color: #008080;">62</span> 
  63. <span style="color: #008080;">63</span> <span style="color: #808080;">+</span><span style="color: #ff00ff;">case</span> <span style="color: #0000ff;">when</span> <span style="color: #008000;">@xtype</span> <span style="color: #808080;">in</span>(<span style="color: #800000; font-weight: bold;">167</span>,<span style="color: #800000; font-weight: bold;">175</span>) <span style="color: #0000ff;">then</span> <span style="color: #ff0000;">‘‘‘‘‘‘‘‘‘</span><span style="color: #ff0000;">+</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span><span style="color: #008000;">@name</span><span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">+</span><span style="color: #ff0000;">‘‘‘‘‘‘‘‘‘</span> <span style="color: #008080;">--</span><span style="color: #008080;">varchar,char </span>
  64. <span style="color: #008080;">64</span> <span style="color: #0000ff;">when</span> <span style="color: #008000;">@xtype</span> <span style="color: #808080;">in</span>(<span style="color: #800000; font-weight: bold;">231</span>,<span style="color: #800000; font-weight: bold;">239</span>) <span style="color: #0000ff;">then</span> <span style="color: #ff0000;">‘‘‘</span><span style="color: #ff0000;">N</span><span style="color: #ff0000;">‘‘‘‘‘‘</span><span style="color: #ff0000;">+</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span><span style="color: #008000;">@name</span><span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">+</span><span style="color: #ff0000;">‘‘‘‘‘‘‘‘‘</span> <span style="color: #008080;">--</span><span style="color: #008080;">nvarchar,nchar </span>
  65. <span style="color: #008080;">65</span> <span style="color: #0000ff;">when</span> <span style="color: #008000;">@xtype</span><span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">61</span> <span style="color: #0000ff;">then</span> <span style="color: #ff0000;">‘‘‘‘‘‘‘‘‘</span><span style="color: #ff0000;">+convert(char(23),</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span><span style="color: #008000;">@name</span><span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,121)+</span><span style="color: #ff0000;">‘‘‘‘‘‘‘‘‘</span> <span style="color: #008080;">--</span><span style="color: #008080;">datetime </span>
  66. <span style="color: #008080;">66</span> <span style="color: #0000ff;">when</span> <span style="color: #008000;">@xtype</span><span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">58</span> <span style="color: #0000ff;">then</span> <span style="color: #ff0000;">‘‘‘‘‘‘‘‘‘</span><span style="color: #ff0000;">+convert(char(16),</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span><span style="color: #008000;">@name</span><span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,120)+</span><span style="color: #ff0000;">‘‘‘‘‘‘‘‘‘</span> <span style="color: #008080;">--</span><span style="color: #008080;">smalldatetime </span>
  67. <span style="color: #008080;">67</span> <span style="color: #0000ff;">when</span> <span style="color: #008000;">@xtype</span><span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">36</span> <span style="color: #0000ff;">then</span> <span style="color: #ff0000;">‘‘‘‘‘‘‘‘‘</span><span style="color: #ff0000;">+convert(char(36),</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span><span style="color: #008000;">@name</span><span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">)+</span><span style="color: #ff0000;">‘‘‘‘‘‘‘‘‘</span> <span style="color: #008080;">--</span><span style="color: #008080;">uniqueidentifier </span>
  68. <span style="color: #008080;">68</span> <span style="color: #0000ff;">else</span> <span style="color: #008000;">@name</span> <span style="color: #0000ff;">end</span> 
  69. <span style="color: #008080;">69</span> 
  70. <span style="color: #008080;">70</span> <span style="color: #0000ff;">end</span> 
  71. <span style="color: #008080;">71</span> 
  72. <span style="color: #008080;">72</span> <span style="color: #0000ff;">end</span> 
  73. <span style="color: #008080;">73</span> 
  74. <span style="color: #008080;">74</span> <span style="color: #0000ff;">fetch</span> <span style="color: #0000ff;">next</span> <span style="color: #0000ff;">from</span> syscolumns_cursor <span style="color: #0000ff;">into</span> <span style="color: #008000;">@name</span>,<span style="color: #008000;">@xtype</span> 
  75. <span style="color: #008080;">75</span> 
  76. <span style="color: #008080;">76</span> <span style="color: #0000ff;">end</span> 
  77. <span style="color: #008080;">77</span> 
  78. <span style="color: #008080;">78</span> <span style="color: #0000ff;">close</span><span style="color: #000000;"> syscolumns_cursor 
  79. </span><span style="color: #008080;">79</span> <span style="color: #0000ff;">deallocate</span><span style="color: #000000;"> syscolumns_cursor 
  80. </span><span style="color: #008080;">80</span> 
  81. <span style="color: #008080;">81</span> <span style="color: #0000ff;">set</span> <span style="color: #008000;">@sql</span><span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">set nocount on select </span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">insert </span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span><span style="color: #008000;">@tablename</span><span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">(</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span><span style="color: #008000;">@column</span><span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">) values(</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">as </span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">--</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span><span style="color: #008000;">@columndata</span><span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">)</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;"> from </span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span><span style="color: #008000;">@tablename</span> 
  82. <span style="color: #008080;">82</span> 
  83. <span style="color: #008080;">83</span> <span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">--</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span><span style="color: #008000;">@sql</span> 
  84. <span style="color: #008080;">84</span> <span style="color: #0000ff;">exec</span>(<span style="color: #008000;">@sql</span><span style="color: #000000;">) 
  85. </span><span style="color: #008080;">85</span> 
  86. <span style="color: #008080;">86</span> <span style="color: #0000ff;">if</span> <span style="color: #008000;">@ident</span> <span style="color: #0000ff;">is</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span> 
  87. <span style="color: #008080;">87</span> <span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">SET IDENTITY_INSERT </span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span><span style="color: #008000;">@TableName</span><span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;"> OFF</span><span style="color: #ff0000;">‘</span> 
  88. <span style="color: #008080;">88</span> 
  89. <span style="color: #008080;">89</span> <span style="color: #0000ff;">GO</span> 
  90. <span style="color: #008080;">90</span> 
  91. <span style="color: #008080;">91</span> <span style="color: #0000ff;">exec</span> UspOutputData 你的表名
View Code 2、执行存储过程 技术分享 复制第一行数据： 
  1. <span style="color: #0000ff;">insert</span> Ad(Id,ParentId,Sorts,Name,Width,Height,Url,Pic,Description,Contents,Time) <span style="color: #0000ff;">values</span><span style="color: #000000;">(
  2.     </span><span style="color: #800000; font-weight: bold;">2</span>,<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">0</span>,N<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">广告1</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #800000; font-weight: bold;">0</span>,N<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">www.baidu.com</span><span style="color: #ff0000;">‘</span>,N<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">../../Upload/Download/20150327111437.jpg</span><span style="color: #ff0000;">‘</span>,N<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">广告1的内容</span><span style="color: #ff0000;">‘</span>,N<span style="color: #ff0000;">‘‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2015-03-27 11:14:40.000</span><span style="color: #ff0000;">‘</span>)
Look！We got it! 存储过程摘自：http://bbs.csdn.net/topics/300135193

 

使用SQL语句创建SQL数据脚本（应对万网主机部分不支持导出备份数据）

标签：

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！