Posted By

pauliehaha on 02/09/09


Tagged


Versions (?)

Create Insert or Update Statement From Table All Rows


 / Published in: SQL
 

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

Report this snippet  

You need to login to post a comment.