1
if exists (select * from sysobjects where id =
2
object_id('spu_GenerateInsert'))
3
drop procedure spu_GenerateInsert
4
GO
5![](/Images/OutliningIndicators/None.gif)
6![](/Images/OutliningIndicators/None.gif)
7
CREATE PROCEDURE spu_GenerateInsert
8
@table varchar(128)
9
AS
10![](/Images/OutliningIndicators/None.gif)
26![](/Images/OutliningIndicators/None.gif)
27
--declare some variables that will be used
28
DECLARE @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![](/Images/OutliningIndicators/None.gif)
37![](/Images/OutliningIndicators/None.gif)
38
--initialize some of the variables
39
SELECT @InsertStmt = 'INSERT INTO ' + @Table + ' (',
40
@Fields = '',
41
@Data = '',
42
@SelList = 'SELECT ',
43
@FldCounter = 0
44![](/Images/OutliningIndicators/None.gif)
45![](/Images/OutliningIndicators/None.gif)
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
48
field needs
49
DECLARE CR_Table CURSOR FAST_FORWARD FOR
50
SELECT COLUMN_NAME,
51
'IsChar' = CASE
52
WHEN DATA_TYPE in ('int', 'money', 'decimal', 'tinyint', 'smallint') THEN
53
0
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
61
FOR READ ONLY
62
OPEN CR_Table
63
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
64
WHILE (@@fetch_status <> -1)
65
BEGIN
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![](/Images/OutliningIndicators/None.gif)
82![](/Images/OutliningIndicators/None.gif)
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
93
END
94
CLOSE CR_Table
95
DEALLOCATE CR_Table
96![](/Images/OutliningIndicators/None.gif)
97![](/Images/OutliningIndicators/None.gif)
98
SELECT @Fields = SUBSTRING(@Fields, 1,(len(@Fields)-1))
99
--SELECT @Fields AS TheFields
100![](/Images/OutliningIndicators/None.gif)
101![](/Images/OutliningIndicators/None.gif)
102
SELECT @SelList = SUBSTRING(@SelList, 1,(len(@SelList)-1))
103
SELECT @SelList = @SelList + ' FROM ' + @table
104
--SELECT @SelList AS TheSelList
105![](/Images/OutliningIndicators/None.gif)
106![](/Images/OutliningIndicators/None.gif)
107
SELECT @InsertStmt = @InsertStmt + @Fields + ')'
108
--SELECT @InsertStmt AS TheInsertStmt
109![](/Images/OutliningIndicators/None.gif)
110![](/Images/OutliningIndicators/None.gif)
111
--RETURN (0)
112
--go
113![](/Images/OutliningIndicators/None.gif)
114![](/Images/OutliningIndicators/None.gif)
115
--for debugging
116
--exec (@SelList)
117![](/Images/OutliningIndicators/None.gif)
118![](/Images/OutliningIndicators/None.gif)
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![](/Images/OutliningIndicators/None.gif)
122![](/Images/OutliningIndicators/None.gif)
123
CREATE TABLE #TheData (TableData varchar(8000))
124
INSERT INTO #TheData (TableData) EXEC (@SelList)
125![](/Images/OutliningIndicators/None.gif)
126![](/Images/OutliningIndicators/None.gif)
127
--Cursor through the data to generate the INSERT statement / VALUES clause
128
DECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR
129
READ ONLY
130
OPEN CR_Data
131
FETCH NEXT FROM CR_Data INTO @TableData
132
WHILE (@@fetch_status <> -1)
133
BEGIN
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
140
END
141
CLOSE CR_Data
142
DEALLOCATE CR_Data
143![](/Images/OutliningIndicators/None.gif)
144![](/Images/OutliningIndicators/None.gif)
145
RETURN (0)
146![](/Images/OutliningIndicators/None.gif)
147![](/Images/OutliningIndicators/None.gif)
148
GO
149![](/Images/OutliningIndicators/None.gif)
150![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
32
![](/Images/OutliningIndicators/None.gif)
33
![](/Images/OutliningIndicators/None.gif)
34
![](/Images/OutliningIndicators/None.gif)
35
![](/Images/OutliningIndicators/None.gif)
36
![](/Images/OutliningIndicators/None.gif)
37
![](/Images/OutliningIndicators/None.gif)
38
![](/Images/OutliningIndicators/None.gif)
39
![](/Images/OutliningIndicators/None.gif)
40
![](/Images/OutliningIndicators/None.gif)
41
![](/Images/OutliningIndicators/None.gif)
42
![](/Images/OutliningIndicators/None.gif)
43
![](/Images/OutliningIndicators/None.gif)
44
![](/Images/OutliningIndicators/None.gif)
45
![](/Images/OutliningIndicators/None.gif)
46
![](/Images/OutliningIndicators/None.gif)
47
![](/Images/OutliningIndicators/None.gif)
48
![](/Images/OutliningIndicators/None.gif)
49
![](/Images/OutliningIndicators/None.gif)
50
![](/Images/OutliningIndicators/None.gif)
51
![](/Images/OutliningIndicators/None.gif)
52
![](/Images/OutliningIndicators/None.gif)
53
![](/Images/OutliningIndicators/None.gif)
54
![](/Images/OutliningIndicators/None.gif)
55
![](/Images/OutliningIndicators/None.gif)
56
![](/Images/OutliningIndicators/None.gif)
57
![](/Images/OutliningIndicators/None.gif)
58
![](/Images/OutliningIndicators/None.gif)
59
![](/Images/OutliningIndicators/None.gif)
60
![](/Images/OutliningIndicators/None.gif)
61
![](/Images/OutliningIndicators/None.gif)
62
![](/Images/OutliningIndicators/None.gif)
63
![](/Images/OutliningIndicators/None.gif)
64
![](/Images/OutliningIndicators/None.gif)
65
![](/Images/OutliningIndicators/None.gif)
66
![](/Images/OutliningIndicators/None.gif)
67
![](/Images/OutliningIndicators/None.gif)
68
![](/Images/OutliningIndicators/None.gif)
69
![](/Images/OutliningIndicators/None.gif)
70
![](/Images/OutliningIndicators/None.gif)
71
![](/Images/OutliningIndicators/None.gif)
72
![](/Images/OutliningIndicators/None.gif)
73
![](/Images/OutliningIndicators/None.gif)
74
![](/Images/OutliningIndicators/None.gif)
75
![](/Images/OutliningIndicators/None.gif)
76
![](/Images/OutliningIndicators/None.gif)
77
![](/Images/OutliningIndicators/None.gif)
78
![](/Images/OutliningIndicators/None.gif)
79
![](/Images/OutliningIndicators/None.gif)
80
![](/Images/OutliningIndicators/None.gif)
81
![](/Images/OutliningIndicators/None.gif)
82
![](/Images/OutliningIndicators/None.gif)
83
![](/Images/OutliningIndicators/None.gif)
84
![](/Images/OutliningIndicators/None.gif)
85
![](/Images/OutliningIndicators/None.gif)
86
![](/Images/OutliningIndicators/None.gif)
87
![](/Images/OutliningIndicators/None.gif)
88
![](/Images/OutliningIndicators/None.gif)
89
![](/Images/OutliningIndicators/None.gif)
90
![](/Images/OutliningIndicators/None.gif)
91
![](/Images/OutliningIndicators/None.gif)
92
![](/Images/OutliningIndicators/None.gif)
93
![](/Images/OutliningIndicators/None.gif)
94
![](/Images/OutliningIndicators/None.gif)
95
![](/Images/OutliningIndicators/None.gif)
96
![](/Images/OutliningIndicators/None.gif)
97
![](/Images/OutliningIndicators/None.gif)
98
![](/Images/OutliningIndicators/None.gif)
99
![](/Images/OutliningIndicators/None.gif)
100
![](/Images/OutliningIndicators/None.gif)
101
![](/Images/OutliningIndicators/None.gif)
102
![](/Images/OutliningIndicators/None.gif)
103
![](/Images/OutliningIndicators/None.gif)
104
![](/Images/OutliningIndicators/None.gif)
105
![](/Images/OutliningIndicators/None.gif)
106
![](/Images/OutliningIndicators/None.gif)
107
![](/Images/OutliningIndicators/None.gif)
108
![](/Images/OutliningIndicators/None.gif)
109
![](/Images/OutliningIndicators/None.gif)
110
![](/Images/OutliningIndicators/None.gif)
111
![](/Images/OutliningIndicators/None.gif)
112
![](/Images/OutliningIndicators/None.gif)
113
![](/Images/OutliningIndicators/None.gif)
114
![](/Images/OutliningIndicators/None.gif)
115
![](/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/dot.gif)
116
![](/Images/OutliningIndicators/None.gif)
117
![](/Images/OutliningIndicators/None.gif)
118
![](/Images/OutliningIndicators/None.gif)
119
![](/Images/OutliningIndicators/None.gif)
120
![](/Images/OutliningIndicators/None.gif)
121
![](/Images/OutliningIndicators/None.gif)
122
![](/Images/OutliningIndicators/None.gif)
123
![](/Images/OutliningIndicators/None.gif)
124
![](/Images/OutliningIndicators/None.gif)
125
![](/Images/OutliningIndicators/None.gif)
126
![](/Images/OutliningIndicators/None.gif)
127
![](/Images/OutliningIndicators/None.gif)
128
![](/Images/OutliningIndicators/None.gif)
129
![](/Images/OutliningIndicators/None.gif)
130
![](/Images/OutliningIndicators/None.gif)
131
![](/Images/OutliningIndicators/None.gif)
132
![](/Images/OutliningIndicators/None.gif)
133
![](/Images/OutliningIndicators/None.gif)
134
![](/Images/OutliningIndicators/None.gif)
135
![](/Images/OutliningIndicators/None.gif)
136
![](/Images/OutliningIndicators/None.gif)
137
![](/Images/OutliningIndicators/None.gif)
138
![](/Images/OutliningIndicators/None.gif)
139
![](/Images/OutliningIndicators/None.gif)
140
![](/Images/OutliningIndicators/None.gif)
141
![](/Images/OutliningIndicators/None.gif)
142
![](/Images/OutliningIndicators/None.gif)
143
![](/Images/OutliningIndicators/None.gif)
144
![](/Images/OutliningIndicators/None.gif)
145
![](/Images/OutliningIndicators/None.gif)
146
![](/Images/OutliningIndicators/None.gif)
147
![](/Images/OutliningIndicators/None.gif)
148
![](/Images/OutliningIndicators/None.gif)
149
![](/Images/OutliningIndicators/None.gif)
150
![](/Images/OutliningIndicators/None.gif)