Posted By

pauliehaha on 02/09/09


Tagged


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

PapTom


Generate Insert Statements


 / Published in: SQL
 

  1. /*
  2.   SQL Server Row Script Creator
  3.  
  4.   This script will generate script to insert/update from a source table in one database to an
  5.   identical destination table in another database or server. It can be run for inserts or updates,
  6.   and can be run for a single row in insert and update mode, or all rows in a table for insert mode.
  7. */
  8.  
  9. declare @tab varchar(50)
  10. ,@pk1Val varChar(10)
  11. ,@pk1Name varChar(50)
  12. ,@qt char(1)
  13. ,@StatementType varChar(10)
  14. SET nocount ON
  15. /*
  16.  Instructions:
  17.  1) open script and connect to the source database
  18.  2) Change the variable values to change the output options for the script below (@tab, @statementtype etc)
  19.  3) execute the script (best to use text output)
  20.  4) copy the script output into a script window, and run on the destination table.
  21.  
  22. @Tab = the name of the source table
  23. @pk1Val = if selecting a single row or doing an update statement, the value of the primary key for that row
  24. @pk1Name = if inserting a single row or doing an update statement, the name of the column for the primary key
  25. @StatementType = either 'INSERT' to create an insert statement or 'UPDATE' for an Update statement
  26. */
  27. SELECT @tab = 'Access_Right_Role', @pk1Val = '', @pk1Name = '', @StatementType = 'INSERT'
  28.  
  29. declare @tabName varchar(50)
  30. , @colName varchar(50)
  31. , @colType varchar(50)
  32. , @collength varChar(50)
  33. , @colOrder int
  34. , @IsIdent char(1)
  35.  
  36.  
  37. CREATE TABLE #output (Line varChar(4000), LineOrder int, rowNumber int)
  38. CREATE TABLE #ColumnValues (ColName varChar(250), ColOrder int, RowNumber int, ColValue varchar(4000), colType varchar(50))
  39.  
  40. declare @out varchar(4000)
  41. ,@lineCounter int
  42. ,@ColValue varchar(4000)
  43. ,@sortCol varchar(50)
  44.  
  45.  
  46. /* get the ordering column */
  47. SELECT @sortCol = sc.Name
  48. FROM sysobjects so
  49. INNER JOIN syscolumns sc
  50. ON so.id= sc.id
  51. INNER JOIN systypes st
  52. ON sc.xtype = st.xusertype
  53. WHERE so.Name = @tab
  54. AND ((sc.STATUS = 0x80) OR (ColOrder = 1 AND NOT sc.STATUS = 0x80 ))
  55.  
  56.  
  57.  
  58. /* put in the repeating values based on the columns*/
  59. declare objCurs CURSOR FOR
  60. SELECT so.name, sc.name, st.name, sc.length, Case when sc.STATUS = 0x80 then 'Y' else 'N' END AS IsIdent, ColOrder
  61. FROM sysobjects so
  62. INNER JOIN syscolumns sc
  63. ON so.id= sc.id
  64. INNER JOIN systypes st
  65. ON sc.xtype = st.xusertype
  66. WHERE so.Name = @tab
  67.  
  68. DECLARE @counter int, @numCols int, @RowNumber int, @LastRowNumber int, @maxRowNumber int, @maxColOrder int
  69.  
  70. SELECT @numCols = count(sc.id)
  71. FROM sysobjects so
  72. INNER JOIN syscolumns sc
  73. ON so.id= sc.id
  74. WHERE so.Name = @tab
  75.  
  76. --select @numCols --debug code
  77.  
  78. open objCurs
  79. Fetch FROM objCurs
  80. INTO @tabname, @colName, @colType, @colLength, @isIdent, @colOrder
  81.  
  82. while @@fetch_status = 0
  83. begin
  84. SET @counter = 0
  85. /* get the value from the table */
  86. IF @IsIdent = 'N'
  87. BEGIN
  88. --select @TabName,@ColName, @ColType, @ColLEngth, @isIdent, @ColOrder --debug code
  89. /* increase better type handling by inserting more case statments, handling different data types */
  90. IF datalength(@pk1Name) = 0 OR datalength(@pk1Val) = 0
  91. begin
  92. /* getting all rows in the table */
  93. exec ('insert into #ColumnValues (ColName, ColOrder, ColValue, ColType)
  94. select ''' + @colName + ''', ' + @ColOrder + ', Convert(nvarchar(4000),' + @colName + ') , ''' + @colType + ''' from ' + @tabName + ' order by ' + @SortCol + ' ' +
  95. ' declare @counter int set @counter = 0 ' +
  96. ' update #ColumnValues Set @Counter = RowNumber = @Counter + (' + @numCols + ' * 10) where ColName = ''' + @colName + '''' )
  97. end
  98. else
  99. begin
  100. /* filtering by a pk val */
  101. exec ('insert into #ColumnValues (RowNumber, ColName, ColORder, ColValue, ColType)
  102. select 0, ''' + @colName + ''', ' + @ColOrder + ', Convert(nvarchar(4000),' + @colName + ') , ''' + @colType + ''' from ' + @tabName +
  103. ' where ' + @pk1Name + ' = ' + @pk1Val)
  104. end
  105.  
  106.  
  107. end /* if @isIdent = 'n' */
  108.  
  109. Fetch Next FROM objCurs
  110. INTO @tabname, @colName, @colType, @colLength, @IsIdent, @ColOrder
  111. end
  112.  
  113. --select * from #ColumnValues --debug code
  114. SELECT @MaxRowNumber = Max(rowNumber) FROM #columnValues --keep highest row number so we know when we are finished
  115. SELECT @MaxColOrder = max(ColOrder) FROM #ColumnValues where RowNumber = @MaxRowNumber
  116.  
  117. /* next cursor for outputting the results from the retval table into the output table */
  118. declare ColVal_Curs cursor FOR
  119. SELECT ColName , ColOrder , RowNumber , ColValue , colType
  120. FROM #ColumnValues
  121. ORDER BY RowNumber, ColOrder
  122.  
  123. open ColVal_Curs
  124.  
  125. --set the last row number to the first in the table, so post loop checking works
  126. SELECT @lastRowNumber = min(rowNumber) FROM #ColumnValues
  127. SET @lineCounter = @LastRowNumber --initialise at the first row
  128.  
  129. fetch FROM ColVal_Curs INTO
  130. @colName, @ColOrder, @RowNumber, @colValue, @ColType
  131.  
  132. while @@Fetch_status = 0
  133. BEGIN /* cursor loop */
  134.  
  135. /* get the quote type to enclose the value from the column type */
  136. SELECT @qt = case @colType
  137. when 'nvarchar' then ''''
  138. when 'nchar' then ''''
  139. when 'DateTime' then ''''
  140. when 'ntext' then ''''
  141. when 'varchar' then ''''
  142. when 'char' then ''''
  143. when 'text' then ''''
  144. else ''
  145. end
  146.  
  147.  
  148. --select @linecounter, @colName, @ColOrder, @RowNumber, @colValue, @ColType
  149. IF NOT @ColValue IS NULL
  150.  
  151. IF @rowNumber = @lineCounter
  152. SELECT @out = case @statementType
  153. when 'UPDATE' THEN 'Update ' + @tab + ' SET '
  154. when 'INSERT' then 'INSERT INTO ' + @tab + ' ('
  155. end
  156. begin
  157. IF @StatementType = 'UPDATE'
  158. BEGIN
  159. SELECT @Out = @out + @colName + ' = ' + @qt + COALESCE(@ColValue, 'NULL') + @qt + ',' -- + @ColType
  160. INSERT INTO #output (Line, LineOrder)
  161. VALUES (@out, @lineCounter)
  162. end
  163. IF @statementType = 'INSERT'
  164. BEGIN
  165. /* put comma in */
  166. IF @lineCounter > @RowNumber --not first line in set of values for row
  167. SELECT @out = @out + ','
  168.  
  169. /*put in the name of the column */
  170. INSERT INTO #output (Line, LineOrder)
  171. VALUES (@out + @colName
  172. , @lineCounter)
  173.  
  174. IF @lineCounter > @RowNumber --not first line in set of values for row
  175. SELECT @out = ','
  176. else
  177. SELECT @out = ''
  178. /* put in the value of the column */
  179. INSERT INTO #output (Line, LineOrder)
  180. VALUES (@out + @qt + COALESCE(@ColValue, 'NULL') + @qt
  181. , @lineCounter + 10 + @numCols)
  182.  
  183. END
  184. end /*not @ColValue is null */
  185. SELECT @lineCounter = @lineCounter + 1
  186. SET @out = ''
  187. /* get the next record*/
  188. fetch FROM ColVal_Curs INTO
  189. @colName, @ColOrder, @RowNumber, @colValue, @ColType
  190. --select @ColOrder, @MaxColOrder, @@Fetch_Status --debug code
  191. IF (@rowNumber > @lastRowNumber) OR (@RowNumber = @MaxRowNumber AND @MaxColOrder = @ColOrder AND @@FEtch_Status = -1)
  192. BEGIN
  193. /* this bit of processing is done whenever the set of columsn in a row changes to the next row of the original table*/
  194. /* ie we are building a record to insert, and the PK changes because we are at the next record */
  195. /* remove the last comma from the last line */
  196. declare @lastLine int
  197.  
  198. IF @statementType = 'UPDATE'
  199. begin
  200. /*remove last comma*/
  201. UPDATE #output
  202. SET Line = LEFT(Line,datalength(Line)-1)
  203. WHERE lineOrder = @LineCounter
  204.  
  205. /* insert a 'where' clause */
  206.  
  207. INSERT INTO #output (line, LineOrder)
  208. SELECT ' WHERE ' + @pk1Name + ' = ' + @pk1Val, Max(LineOrder) + 1 FROM #output
  209.  
  210. end
  211. IF @statementType = 'INSERT'
  212. BEGIN
  213. /* put in a 'values' statement between the column names and the column values */
  214. INSERT INTO #output (Line, LineOrder)
  215. VALUES (') VALUES (', @LastRowNumber + @numCols + 5)
  216. /* close off the lot */
  217. INSERT INTO #output (line, lineorder)
  218. SELECT ')', Max(LineOrder) + 1 FROM #output
  219. END
  220. SET @lastRowNumber = @RowNumber
  221. SET @lineCounter = @RowNumber /* reset linecounter for next set */
  222. UPDATE #output
  223. SET RowNumber = @rowNumber
  224. WHERE RowNumber IS NULL
  225. End /* if rownumber > last row number */
  226.  
  227. end /* cursor loop */
  228.  
  229. close objCurs
  230. deallocate objCurs
  231.  
  232. close ColVal_Curs
  233. deallocate ColVal_Curs
  234.  
  235.  
  236. /* the following is an extra loop over the original code to output the code as one statement per row */
  237. CREATE TABLE #combineOutput (rowNumber int, line varchar(4000))
  238. /* get the statements out from the list*/
  239. declare @output varchar(8000), @codeLine varchar(4000), @thisRowNum int, @lastRowNum int
  240. SELECT @output = ''
  241. declare line_curs cursor FOR
  242. SELECT line, RowNumber FROM #output order by RowNumber, lineorder
  243.  
  244. open line_curs
  245. fetch FROM line_curs INTO @codeLine, @thisRowNum
  246. SELECT @lastRowNum = @thisRowNum
  247. while @@fetch_status = 0
  248. begin
  249. IF @thisROwNum > @lastRowNum
  250. BEGIN
  251. /* insert a row into the aggregate table if a new row number */
  252. INSERT INTO #combineOutput (rowNumber, line) values (@rowNumber, @output)
  253. SET @output = ''
  254. END
  255. SELECT @output = @output + @codeLine + ' '
  256. SELECT @lastRowNum = @thisRowNum
  257. fetch FROM line_curs INTO @codeLine, @thisRowNum
  258. end
  259. /* the last row needs to be inserted */
  260. INSERT INTO #combineOutput (rowNumber, line) values (@rowNumber, @output)
  261.  
  262. close line_curs
  263. deallocate line_curs
  264.  
  265. SELECT line AS [Copy AND paste code FROM below] FROM #combineOutput order by rowNumber
  266.  
  267. /* bug tracking code - uncomment to diagnose problems
  268. select distinct RowNumber from #ColumnValues order by 1
  269. select * from #ColumnValues
  270. order by RowNumber, ColOrder, ColName, ColValue
  271. */
  272. DROP TABLE #output
  273. DROP TABLE #combineOutput
  274. DROP TABLE #ColumnValues
  275. SET nocount off

Report this snippet  

You need to login to post a comment.