API tools faq
paste
Advertisement
Guest User

ms sql primer

a guest
Oct 17th, 2016
23
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 9.00 KB | None | 0 0
raw download clone embed print report
  1.  
  2. SET DATEFORMAT ymd
  3.  
  4. DECLARE @datevar datetime
  5.  
  6. if (SELECT COUNT(*)  FROM _YearOffset  WHERE Offset=2000) > 0
  7.     set @datevar = '4016-04-17' --print 'Есть смещение'
  8. Else
  9.     set @datevar = '2016-04-17' --print 'Есть смещение'
  10.  
  11. select COUNT(*) as DoUdaleniya from _InfoReg4206
  12.  
  13. BEGIN TRAN
  14. BEGIN TRY
  15.  
  16. select reg.* INTO #tmp_InfoReg4206_20161017113542 from _InfoReg4206 as reg
  17. where _Fld4207_RRRef IN (
  18. SELECT _IDRRef as _Fld4207_RRRef FROM _Document9915 WHERE (_Date_Time >= @datevar)
  19. UNION
  20. SELECT _IDRRef FROM _Document4625 WHERE (_Date_Time >= @datevar)
  21. UNION
  22. SELECT _IDRRef FROM _Document7991 WHERE (_Date_Time >= @datevar)
  23. UNION
  24. SELECT _IDRRef FROM _Document6846 WHERE (_Date_Time >= @datevar)
  25. UNION
  26. SELECT _IDRRef FROM _Document9227 WHERE (_Date_Time >= @datevar)
  27. UNION
  28. SELECT _IDRRef FROM _Document7408 WHERE (_Date_Time >= @datevar)
  29. UNION
  30. SELECT _IDRRef FROM _Document9549 WHERE (_Date_Time >= @datevar)
  31. UNION
  32. SELECT _IDRRef FROM _Document4896 WHERE (_Date_Time >= @datevar)
  33. UNION
  34. SELECT _IDRRef FROM _Document4629 WHERE (_Date_Time >= @datevar)
  35. UNION
  36. SELECT _IDRRef FROM _Document4989 WHERE (_Date_Time >= @datevar)
  37. UNION
  38. SELECT _IDRRef FROM _Document5072 WHERE (_Date_Time >= @datevar)
  39. UNION
  40. SELECT _IDRRef FROM _Document6306 WHERE (_Date_Time >= @datevar)
  41. UNION
  42. SELECT _IDRRef FROM _Document8430 WHERE (_Date_Time >= @datevar)
  43. UNION
  44. SELECT _IDRRef FROM _Document117 WHERE (_Date_Time >= @datevar)
  45. UNION
  46. SELECT _IDRRef FROM _Document6950 WHERE (_Date_Time >= @datevar)
  47. UNION
  48. SELECT _IDRRef FROM _Document8553 WHERE (_Date_Time >= @datevar)
  49. UNION
  50. SELECT _IDRRef FROM _Document7411 WHERE (_Date_Time >= @datevar)
  51. UNION
  52. SELECT _IDRRef FROM _Document9090 WHERE (_Date_Time >= @datevar)
  53. UNION
  54. SELECT _IDRRef FROM _Document6181 WHERE (_Date_Time >= @datevar)
  55. UNION
  56. SELECT _IDRRef FROM _Document6417 WHERE (_Date_Time >= @datevar)
  57. UNION
  58. SELECT _IDRRef FROM _Document4627 WHERE (_Date_Time >= @datevar)
  59. UNION
  60. SELECT _IDRRef FROM _Document4628 WHERE (_Date_Time >= @datevar)
  61. UNION
  62. SELECT _IDRRef FROM _Document6502 WHERE (_Date_Time >= @datevar)
  63. UNION
  64. SELECT _IDRRef FROM _Document7206 WHERE (_Date_Time >= @datevar)
  65. UNION
  66. SELECT _IDRRef FROM _Document6599 WHERE (_Date_Time >= @datevar)
  67. UNION
  68. SELECT _IDRRef FROM _Document6322 WHERE (_Date_Time >= @datevar)
  69. UNION
  70. SELECT _IDRRef FROM _Document5124 WHERE (_Date_Time >= @datevar)
  71. UNION
  72. SELECT _IDRRef FROM _Document5095 WHERE (_Date_Time >= @datevar)
  73. UNION
  74. SELECT _IDRRef FROM _Document7207 WHERE (_Date_Time >= @datevar)
  75. UNION
  76. SELECT _IDRRef FROM _Document60 WHERE (_Date_Time >= @datevar)
  77. UNION
  78. SELECT _IDRRef FROM _Document5043 WHERE (_Date_Time >= @datevar)
  79. UNION
  80. SELECT _IDRRef FROM _Document4956 WHERE (_Date_Time >= @datevar)
  81. UNION
  82. SELECT _IDRRef FROM _Document6540 WHERE (_Date_Time >= @datevar)
  83. UNION
  84. SELECT _IDRRef FROM _Document4843 WHERE (_Date_Time >= @datevar)
  85. UNION
  86. SELECT _IDRRef FROM _Document10522 WHERE (_Date_Time >= @datevar)
  87. UNION
  88. SELECT _IDRRef FROM _Document64 WHERE (_Date_Time >= @datevar)
  89. UNION
  90. SELECT _IDRRef FROM _Document4503 WHERE (_Date_Time >= @datevar)
  91. UNION
  92. SELECT _IDRRef FROM _Document7177 WHERE (_Date_Time >= @datevar)
  93. UNION
  94. SELECT _IDRRef FROM _Document118 WHERE (_Date_Time >= @datevar)
  95. UNION
  96. SELECT _IDRRef FROM _Document65 WHERE (_Date_Time >= @datevar)
  97. UNION
  98. SELECT _IDRRef FROM _Document7056 WHERE (_Date_Time >= @datevar)
  99. UNION
  100. SELECT _IDRRef FROM _Document4007 WHERE (_Date_Time >= @datevar)
  101. UNION
  102. SELECT _IDRRef FROM _Document3686 WHERE (_Date_Time >= @datevar)
  103. UNION
  104. SELECT _IDRRef FROM _Document4009 WHERE (_Date_Time >= @datevar)
  105. UNION
  106. SELECT _IDRRef FROM _Document108 WHERE (_Date_Time >= @datevar)
  107. UNION
  108. SELECT _IDRRef FROM _Document114 WHERE (_Date_Time >= @datevar)
  109. UNION
  110. SELECT _IDRRef FROM _Document105 WHERE (_Date_Time >= @datevar)
  111. UNION
  112. SELECT _IDRRef FROM _Document3685 WHERE (_Date_Time >= @datevar)
  113. UNION
  114. SELECT _IDRRef FROM _Document3577 WHERE (_Date_Time >= @datevar)
  115. UNION
  116. SELECT _IDRRef FROM _Document104 WHERE (_Date_Time >= @datevar)
  117. UNION
  118. SELECT _IDRRef FROM _Document109 WHERE (_Date_Time >= @datevar)
  119. UNION
  120. SELECT _IDRRef FROM _Document119 WHERE (_Date_Time >= @datevar)
  121. UNION
  122. SELECT _IDRRef FROM _Document115 WHERE (_Date_Time >= @datevar)
  123. UNION
  124. SELECT _IDRRef FROM _Document3754 WHERE (_Date_Time >= @datevar)
  125. UNION
  126. SELECT _IDRRef FROM _Document3719 WHERE (_Date_Time >= @datevar)
  127. UNION
  128. SELECT _IDRRef FROM _Document110 WHERE (_Date_Time >= @datevar)
  129. UNION
  130. SELECT _IDRRef FROM _Document3663 WHERE (_Date_Time >= @datevar)
  131. UNION
  132. SELECT _IDRRef FROM _Document116 WHERE (_Date_Time >= @datevar)
  133. UNION
  134. SELECT _IDRRef FROM _Document112 WHERE (_Date_Time >= @datevar)
  135. UNION
  136. SELECT _IDRRef FROM _Document113 WHERE (_Date_Time >= @datevar)
  137. UNION
  138. SELECT _IDRRef FROM _Document120 WHERE (_Date_Time >= @datevar)
  139. UNION
  140. SELECT _IDRRef FROM _Document107 WHERE (_Date_Time >= @datevar)
  141. UNION
  142. SELECT _IDRRef FROM _Document3864 WHERE (_Date_Time >= @datevar)
  143. UNION
  144. SELECT _IDRRef FROM _Document3865 WHERE (_Date_Time >= @datevar)
  145. UNION
  146. SELECT _IDRRef FROM _Document4327 WHERE (_Date_Time >= @datevar)
  147. UNION
  148. SELECT _IDRRef FROM _Document4008 WHERE (_Date_Time >= @datevar)
  149. UNION
  150. SELECT _IDRRef FROM _Document4326 WHERE (_Date_Time >= @datevar)
  151. UNION
  152. SELECT _IDRRef FROM _Document3544 WHERE (_Date_Time >= @datevar)
  153. UNION
  154. SELECT _IDRRef FROM _Document111 WHERE (_Date_Time >= @datevar)
  155. UNION
  156. SELECT _IDRRef FROM _Document4436 WHERE (_Date_Time >= @datevar)
  157. UNION
  158. SELECT _IDRRef FROM _Document106 WHERE (_Date_Time >= @datevar)
  159. UNION
  160. SELECT _IDRRef FROM _Document10350 WHERE (_Date_Time >= @datevar)
  161. UNION
  162. SELECT _IDRRef FROM _Document6644 WHERE (_Date_Time >= @datevar)
  163. UNION
  164. SELECT _IDRRef FROM _Document10063 WHERE (_Date_Time >= @datevar)
  165. UNION
  166. SELECT _IDRRef FROM _Document6752 WHERE (_Date_Time >= @datevar)
  167. UNION
  168. SELECT _IDRRef FROM _Document7634 WHERE (_Date_Time >= @datevar)
  169. UNION
  170. SELECT _IDRRef FROM _Document6246 WHERE (_Date_Time >= @datevar)
  171. UNION
  172. SELECT _IDRRef FROM _Document4552 WHERE (_Date_Time >= @datevar)
  173. UNION
  174. SELECT _IDRRef FROM _Document5916 WHERE (_Date_Time >= @datevar)
  175. UNION
  176. SELECT _IDRRef FROM _Document5684 WHERE (_Date_Time >= @datevar)
  177. UNION
  178. SELECT _IDRRef FROM _Document5446 WHERE (_Date_Time >= @datevar)
  179. UNION
  180. SELECT _IDRRef FROM _Document7409 WHERE (_Date_Time >= @datevar)
  181. UNION
  182. SELECT _IDRRef FROM _Document7069 WHERE (_Date_Time >= @datevar)
  183. UNION
  184. SELECT _IDRRef FROM _Document5444 WHERE (_Date_Time >= @datevar)
  185. UNION
  186. SELECT _IDRRef FROM _Document6575 WHERE (_Date_Time >= @datevar)
  187. UNION
  188. SELECT _IDRRef FROM _Document6751 WHERE (_Date_Time >= @datevar)
  189. UNION
  190. SELECT _IDRRef FROM _Document8555 WHERE (_Date_Time >= @datevar)
  191. UNION
  192. SELECT _IDRRef FROM _Document5998 WHERE (_Date_Time >= @datevar)
  193. UNION
  194. SELECT _IDRRef FROM _Document10546 WHERE (_Date_Time >= @datevar)
  195. UNION
  196. SELECT _IDRRef FROM _Document9429 WHERE (_Date_Time >= @datevar)
  197. UNION
  198. SELECT _IDRRef FROM _Document6247 WHERE (_Date_Time >= @datevar)
  199. UNION
  200. SELECT _IDRRef FROM _Document6248 WHERE (_Date_Time >= @datevar)
  201. UNION
  202. SELECT _IDRRef FROM _Document5445 WHERE (_Date_Time >= @datevar)
  203. UNION
  204. SELECT _IDRRef FROM _Document7410 WHERE (_Date_Time >= @datevar)
  205. UNION
  206. SELECT _IDRRef FROM _Document8556 WHERE (_Date_Time >= @datevar)
  207. UNION
  208. SELECT _IDRRef FROM _Document7632 WHERE (_Date_Time >= @datevar)
  209. UNION
  210. SELECT _IDRRef FROM _Document97 WHERE (_Date_Time >= @datevar)
  211. UNION
  212. SELECT _IDRRef FROM _Document98 WHERE (_Date_Time >= @datevar)
  213. UNION
  214. SELECT _IDRRef FROM _Document9428 WHERE (_Date_Time >= @datevar)
  215. UNION
  216. SELECT _IDRRef FROM _Document4527 WHERE (_Date_Time >= @datevar)
  217. UNION
  218. SELECT _IDRRef FROM _Document4553 WHERE (_Date_Time >= @datevar)
  219. UNION
  220. SELECT _IDRRef FROM _Document4626 WHERE (_Date_Time >= @datevar)
  221. UNION
  222. SELECT _IDRRef FROM _Document7633 WHERE (_Date_Time >= @datevar)
  223. UNION
  224. SELECT _IDRRef FROM _Document5822 WHERE (_Date_Time >= @datevar)
  225. UNION
  226. SELECT _IDRRef FROM _Document9817 WHERE (_Date_Time >= @datevar)
  227. UNION
  228. SELECT _IDRRef FROM _Document5627 WHERE (_Date_Time >= @datevar)
  229. UNION
  230. SELECT _IDRRef FROM _Document5524 WHERE (_Date_Time >= @datevar)
  231. UNION
  232. SELECT _IDRRef FROM _Document7992 WHERE (_Date_Time >= @datevar)
  233. UNION
  234. SELECT _IDRRef FROM _Document5823 WHERE (_Date_Time >= @datevar)
  235. UNION
  236. SELECT _IDRRef FROM _Document6879 WHERE (_Date_Time >= @datevar)
  237. UNION
  238. SELECT _IDRRef FROM _Document10769 WHERE (_Date_Time >= @datevar)
  239. UNION
  240. SELECT _IDRRef FROM _Document10837 WHERE (_Date_Time >= @datevar)
  241. UNION
  242. SELECT _IDRRef FROM _Document10838 WHERE (_Date_Time >= @datevar)
  243. UNION
  244. SELECT _IDRRef FROM _Document11331 WHERE (_Date_Time >= @datevar)
  245. ) and
  246. _Fld4208RRef in (SELECT _IDRRef as _Fld4208RRef
  247. FROM _Node4016
  248. where _Marked=CAST(0 as binary(1)))
  249. TRUNCATE TABLE _InfoReg4206
  250. INSERT INTO _InfoReg4206 SELECT * FROM #tmp_InfoReg4206_20161017113542
  251. END TRY
  252.  
  253. BEGIN CATCH
  254.     SELECT ERROR_MESSAGE()
  255.     ROLLBACK TRAN
  256. END CATCH
  257.  
  258. IF @@TRANCOUNT > 0 COMMIT TRAN
  259.  
  260. select COUNT(*) as PosleUdaleniya from _InfoReg4206
  261.  
  262. SELECT @@TRANCOUNT as NezakonchennieTRAN -- должно быть 0 !!
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!