Export data to INSERT statements

  1if exists (select * from sysobjects where id = 
  2object_id('spu_GenerateInsert')) 
  3 drop procedure spu_GenerateInsert 
  4GO 
  5
  6
  7CREATE PROCEDURE spu_GenerateInsert 
  8 @table varchar(128) 
  9AS 
 10
 26
 27--declare some variables that will be used 
 28DECLARE @InsertStmt varchar(8000), 
 29 @Fields varchar(8000), 
 30 @SelList varchar(8000), 
 31 @Data varchar(8000), 
 32 @ColName varchar(128), 
 33 @IsChar tinyint, 
 34 @FldCounter int, 
 35 @TableData varchar(8000) 
 36
 37
 38--initialize some of the variables 
 39SELECT @InsertStmt = 'INSERT INTO ' + @Table + ' (', 
 40 @Fields = '', 
 41 @Data = '', 
 42 @SelList = 'SELECT ', 
 43 @FldCounter = 0 
 44
 45
 46--create a cursor that loops through the fields in the table 
 47--and retrieves the column names and determines the delimiter type that the 
 48field needs 
 49DECLARE CR_Table CURSOR FAST_FORWARD FOR 
 50 SELECT COLUMN_NAME, 
 51 'IsChar' = CASE 
 52  WHEN DATA_TYPE in ('int', 'money', 'decimal', 'tinyint', 'smallint') THEN 
 530 
 54  WHEN DATA_TYPE in ('char', 'varchar' ) THEN 1 
 55  WHEN DATA_TYPE in ('datetime', 'smalldatetime') THEN 2 
 56  ELSE 9 END 
 57 FROM INFORMATION_SCHEMA.COLUMNS 
 58 WHERE table_name = @table 
 59  AND DATA_TYPE <> 'timestamp' 
 60 ORDER BY ORDINAL_POSITION 
 61FOR READ ONLY 
 62OPEN CR_Table 
 63FETCH NEXT FROM CR_Table INTO @ColName, @IsChar 
 64WHILE (@@fetch_status <> -1) 
 65BEGIN 
 66 IF (@@fetch_status <> -2) 
 67 BEGIN 
 68  IF @FldCounter = 0 
 69  BEGIN 
 70   SELECT @Fields =  @Fields + @ColName + ', ' 
 71   SELECT @SelList =  CASE 
 72    WHEN @IsChar = 1 THEN @SelList + ' ''"'' + ISNULL( REPLACE('+  @ColName 
 73+ ', ''"'', ''""''),"") + ''",'' +' + ' ' 
 74    WHEN @IsChar = 2 THEN @SelList + ' ''"'' +  ISNULL(CONVERT(varchar(20),' 
 75+ @ColName + '),''12/30/1899'') + ''"'' +' + '  ' 
 76    ELSE @SelList + 'ISNULL(CONVERT(varchar(2000),'+@ColName + '),0)' +  '+ 
 77'''' + ' END 
 78   SELECT @FldCounter = @FldCounter + 1 
 79   FETCH NEXT FROM CR_Table INTO @ColName, @IsChar 
 80  END 
 81
 82
 83  SELECT @Fields =  @Fields + @ColName + ', ' 
 84  SELECT @SelList =  CASE 
 85   WHEN @IsChar = 1 THEN @SelList + ' '',"'' + ISNULL(REPLACE(' +  @ColName 
 86+ ', ''"'',''""''), "") + ''"'' +' + ' ' 
 87   WHEN @IsChar = 2 THEN @SelList + ' '',"'' +  ISNULL(CONVERT(varchar(20),' 
 88+ @ColName + '),''12/30/1899'') + ''"'' +' + '  ' 
 89   ELSE @SelList  + ' '','' +   ISNULL(CONVERT(varchar(2000),'+@ColName + 
 90'),0)' + '+' END 
 91 END 
 92 FETCH NEXT FROM CR_Table INTO @ColName, @IsChar 
 93END 
 94CLOSE CR_Table 
 95DEALLOCATE CR_Table 
 96
 97
 98SELECT @Fields =  SUBSTRING(@Fields, 1,(len(@Fields)-1)) 
 99--SELECT @Fields AS TheFields 
100
101
102SELECT @SelList =  SUBSTRING(@SelList, 1,(len(@SelList)-1)) 
103SELECT @SelList = @SelList + ' FROM ' + @table 
104--SELECT @SelList AS TheSelList 
105
106
107SELECT @InsertStmt = @InsertStmt + @Fields + ')' 
108--SELECT @InsertStmt AS TheInsertStmt 
109
110
111--RETURN (0) 
112--go 
113
114
115--for debugging 
116--exec (@SelList) 
117
118
119--now we need to create and load the temp table that will hold the data 
120--that we are going to generate into an insert statement 
121
122
123CREATE TABLE #TheData (TableData varchar(8000)) 
124INSERT INTO #TheData (TableData) EXEC (@SelList) 
125
126
127--Cursor through the data to generate the INSERT statement / VALUES clause 
128DECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR 
129READ ONLY 
130OPEN CR_Data 
131FETCH NEXT FROM CR_Data INTO @TableData 
132WHILE (@@fetch_status <> -1) 
133BEGIN 
134 IF (@@fetch_status <> -2) 
135 BEGIN 
136  PRINT @InsertStmt 
137  PRINT ' VALUES (' +  @TableData + ')' + char(13) + 'GO' 
138 END 
139 FETCH NEXT FROM CR_Data INTO @TableData 
140END 
141CLOSE CR_Data 
142DEALLOCATE CR_Data 
143
144
145RETURN (0) 
146
147
148GO 
149
150