当前位置: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数据脚本(应对万网主机部分不支持导出备份数据)

标签:

人气教程排行