Posted By

CDIDevs on 12/31/09


Tagged

get sql table data as inserts


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

Tyster


Script table as INSERTS


 / Published in: SQL
 

URL: http://www.nigelrivett.net/SQLTsql/sp_CreateDataLoadScript.html

  1. declare @TblName varchar(128)
  2. SET @TblName = 'Categories'
  3.  
  4. CREATE TABLE #a (id int identity (1,1), ColType int, ColName varchar(128))
  5.  
  6. INSERT #a (ColType, ColName)
  7. SELECT case when DATA_TYPE LIKE '%char%' then 1 else 0 end ,
  8. COLUMN_NAME
  9. FROM information_schema.COLUMNS
  10. WHERE TABLE_NAME = @TblName
  11. ORDER BY ORDINAL_POSITION
  12.  
  13. IF NOT EXISTS (SELECT * FROM #a)
  14. begin
  15. raiserror('No columns found for table %s', 16,-1, @TblName)
  16. RETURN
  17. end
  18.  
  19. declare @id int ,
  20. @maxid int ,
  21. @cmd1 varchar(7000) ,
  22. @cmd2 varchar(7000)
  23.  
  24. SELECT @id = 0 ,
  25. @maxid = max(id)
  26. FROM #a
  27.  
  28. SELECT @cmd1 = 'select '' insert ' + @TblName + ' ( '
  29. SELECT @cmd2 = ' + '' select '' + '
  30. while @id < @maxid
  31. begin
  32. SELECT @id = min(id) FROM #a where id > @id
  33.  
  34. SELECT @cmd1 = @cmd1 + ColName + ','
  35. FROM #a
  36. WHERE id = @id
  37.  
  38. SELECT @cmd2 = @cmd2
  39. + ' case when ' + ColName + ' is null '
  40. + ' then ''null'' '
  41. + ' else '
  42. + case when ColType = 1 then ''''''''' + ' + ColName + ' + ''''''''' else 'convert(varchar(20),' + ColName + ')' end
  43. + ' end + '','' + '
  44. FROM #a
  45. WHERE id = @id
  46. end
  47.  
  48.  
  49. SELECT @cmd1 = LEFT(@cmd1,len(@cmd1)-1) + ' ) '' '
  50. SELECT @cmd2 = LEFT(@cmd2,len(@cmd2)-8) + ' from ' + @tblName
  51.  
  52. SELECT '/*' + @cmd1 + @cmd2 + '*/'
  53.  
  54. exec (@cmd1 + @cmd2)
  55. DROP TABLE #a

Report this snippet  

You need to login to post a comment.