Posted By

pauliehaha on 02/09/09


Tagged


Versions (?)

Create Insert or Update Statement From Table All Rows (one line)


 / 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 = 'UPDATE'
  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, rowNumber 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. UPDATE #output
  221. SET RowNumber = @rowNumber
  222. WHERE RowNumber IS NULL
  223. End /* if rownumber > last row number */
  224.  
  225. end /* cursor loop */
  226.  
  227. close objCurs
  228. deallocate objCurs
  229.  
  230. close ColVal_Curs
  231. deallocate ColVal_Curs
  232.  
  233.  
  234. /* the following is an extra loop over the original code to output the code as one statement per row */
  235. CREATE TABLE #combineOutput (rowNumber int, line varchar(4000))
  236. /* get the statements out from the list*/
  237. declare @output varchar(8000), @codeLine varchar(4000), @thisRowNum int, @lastRowNum int
  238. SELECT @output = ''
  239. declare line_curs cursor FOR
  240. SELECT line, RowNumber FROM #output order by RowNumber, lineorder
  241.  
  242. open line_curs
  243. fetch FROM line_curs INTO @codeLine, @thisRowNum
  244. SELECT @lastRowNum = @thisRowNum
  245. while @@fetch_status = 0
  246. begin
  247. IF @thisROwNum > @lastRowNum
  248. BEGIN
  249. /* insert a row into the aggregate table if a new row number */
  250. INSERT INTO #combineOutput (rowNumber, line) values (@rowNumber, @output)
  251. SET @output = ''
  252. END
  253. SELECT @output = @output + @codeLine + ' '
  254. SELECT @lastRowNum = @thisRowNum
  255. fetch FROM line_curs INTO @codeLine, @thisRowNum
  256. end
  257. /* the last row needs to be inserted */
  258. INSERT INTO #combineOutput (rowNumber, line) values (@rowNumber, @output)
  259.  
  260. close line_curs
  261. deallocate line_curs
  262.  
  263. SELECT line AS [Copy AND paste code FROM below] FROM #combineOutput order by rowNumber
  264.  
  265. /* bug tracking code - uncomment to diagnose problems
  266. select distinct RowNumber from #ColumnValues order by 1
  267. select * from #ColumnValues
  268. order by RowNumber, ColOrder, ColName, ColValue
  269. */
  270. DROP TABLE #output
  271. DROP TABLE #combineOutput
  272. DROP TABLE #ColumnValues
  273. SET nocount off

Report this snippet  

You need to login to post a comment.