Script table as INSERTS


/ Published in: SQL
Save to your folder(s)



Copy this code and paste it in your 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

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.