· 9 years ago · Oct 20, 2016, 02:42 PM
1SET NOCOUNT ON
2GO
3
4PRINT 'Using Monitor database'
5USE MONITOR
6GO
7
8PRINT 'Checking for the existence of this procedure'
9IF (SELECT OBJECT_ID('sp_generate_inserts','P')) IS NOT NULL --means, the procedure already exists
10 BEGIN
11 PRINT 'Procedure already exists. So, dropping it'
12 DROP PROC sp_generate_inserts
13 END
14GO
15
16CREATE PROC sp_generate_inserts
17(
18 @table_name varchar(776), -- The table/view for which the INSERT statements will be generated using the existing data
19 @target_table varchar(776) = NULL, -- Use this parameter to specify a different table name into which the data will be inserted
20 @include_column_list bit = 1, -- Use this parameter to include/ommit column list in the generated INSERT statement
21 @from varchar(800) = NULL, -- Use this parameter to filter the rows based on a filter condition (using WHERE)
22 @include_timestamp bit = 0, -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement
23 @debug_mode bit = 0, -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
24 @owner varchar(64) = NULL, -- Use this parameter if you are not the owner of the table
25 @ommit_images bit = 0, -- Use this parameter to generate INSERT statements by omitting the 'image' columns
26 @ommit_identity bit = 0, -- Use this parameter to ommit the identity columns
27 @top int = NULL, -- Use this parameter to generate INSERT statements only for the TOP n rows
28 @cols_to_include varchar(8000) = NULL, -- List of columns to be included in the INSERT statement
29 @cols_to_exclude varchar(8000) = NULL, -- List of columns to be excluded from the INSERT statement
30 @disable_constraints bit = 0, -- When 1, disables foreign key constraints and enables them after the INSERT statements
31 @ommit_computed_cols bit = 0 -- When 1, computed columns will not be included in the INSERT statement
32
33)
34AS
35BEGIN
36
37/***********************************************************************************************************
38Procedure: sp_generate_inserts (Build 22)
39 (Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.)
40
41Purpose: To generate INSERT statements from existing data.
42 These INSERTS can be executed to regenerate the data at some other location.
43 This procedure is also useful to create a database setup, where in you can
44 script your data along with your table definitions.
45
46Written by: Narayana Vyas Kondreddi
47 http://vyaskn.tripod.com
48
49Acknowledgements:
50 Divya Kalra -- For beta testing
51 Mark Charsley -- For reporting a problem with scripting uniqueidentifier columns with NULL values
52 Artur Zeygman -- For helping me simplify a bit of code for handling non-dbo owned tables
53 Joris Laperre -- For reporting a regression bug in handling text/ntext columns
54
55Tested on: SQL Server 7.0 and SQL Server 2000 and SQL Server 2005
56
57Date created: January 17th 2001 21:52 GMT
58
59Date modified: May 1st 2002 19:50 GMT
60
61Email: vyaskn@hotmail.com
62
63NOTE: This procedure may not work with tables with too many columns.
64 Results can be unpredictable with huge text columns or SQL Server 2000's sql_variant data types
65 Whenever possible, Use @include_column_list parameter to ommit column list in the INSERT statement, for better results
66 IMPORTANT: This procedure is not tested with internation data (Extended characters or Unicode). If needed
67 you might want to convert the datatypes of character variables in this procedure to their respective unicode counterparts
68 like nchar and nvarchar
69
70 ALSO NOTE THAT THIS PROCEDURE IS NOT UPDATED TO WORK WITH NEW DATA TYPES INTRODUCED IN SQL SERVER 2005 / YUKON
71
72
73Example 1: To generate INSERT statements for table 'titles':
74
75 EXEC sp_generate_inserts 'titles'
76
77Example 2: To ommit the column list in the INSERT statement: (Column list is included by default)
78 IMPORTANT: If you have too many columns, you are advised to ommit column list, as shown below,
79 to avoid erroneous results
80
81 EXEC sp_generate_inserts 'titles', @include_column_list = 0
82
83Example 3: To generate INSERT statements for 'titlesCopy' table from 'titles' table:
84
85 EXEC sp_generate_inserts 'titles', 'titlesCopy'
86
87Example 4: To generate INSERT statements for 'titles' table for only those titles
88 which contain the word 'Computer' in them:
89 NOTE: Do not complicate the FROM or WHERE clause here. It's assumed that you are good with T-SQL if you are using this parameter
90
91 EXEC sp_generate_inserts 'titles', @from = "from titles where title like '%Computer%'"
92
93Example 5: To specify that you want to include TIMESTAMP column's data as well in the INSERT statement:
94 (By default TIMESTAMP column's data is not scripted)
95
96 EXEC sp_generate_inserts 'titles', @include_timestamp = 1
97
98Example 6: To print the debug information:
99
100 EXEC sp_generate_inserts 'titles', @debug_mode = 1
101
102Example 7: If you are not the owner of the table, use @owner parameter to specify the owner name
103 To use this option, you must have SELECT permissions on that table
104
105 EXEC sp_generate_inserts Nickstable, @owner = 'Nick'
106
107Example 8: To generate INSERT statements for the rest of the columns excluding images
108 When using this otion, DO NOT set @include_column_list parameter to 0.
109
110 EXEC sp_generate_inserts imgtable, @ommit_images = 1
111
112Example 9: To generate INSERT statements excluding (ommiting) IDENTITY columns:
113 (By default IDENTITY columns are included in the INSERT statement)
114
115 EXEC sp_generate_inserts mytable, @ommit_identity = 1
116
117Example 10: To generate INSERT statements for the TOP 10 rows in the table:
118
119 EXEC sp_generate_inserts mytable, @top = 10
120
121Example 11: To generate INSERT statements with only those columns you want:
122
123 EXEC sp_generate_inserts titles, @cols_to_include = "'title','title_id','au_id'"
124
125Example 12: To generate INSERT statements by omitting certain columns:
126
127 EXEC sp_generate_inserts titles, @cols_to_exclude = "'title','title_id','au_id'"
128
129Example 13: To avoid checking the foreign key constraints while loading data with INSERT statements:
130
131 EXEC sp_generate_inserts titles, @disable_constraints = 1
132
133Example 14: To exclude computed columns from the INSERT statement:
134 EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1
135***********************************************************************************************************/
136
137SET NOCOUNT ON
138
139--Making sure user only uses either @cols_to_include or @cols_to_exclude
140IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL))
141 BEGIN
142 RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1)
143 RETURN -1 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified
144 END
145
146--Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format
147IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0))
148 BEGIN
149 RAISERROR('Invalid use of @cols_to_include property',16,1)
150 PRINT 'Specify column names surrounded by single quotes and separated by commas'
151 PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',''title''"'
152 RETURN -1 --Failure. Reason: Invalid use of @cols_to_include property
153 END
154
155IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0))
156 BEGIN
157 RAISERROR('Invalid use of @cols_to_exclude property',16,1)
158 PRINT 'Specify column names surrounded by single quotes and separated by commas'
159 PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'',''title''"'
160 RETURN -1 --Failure. Reason: Invalid use of @cols_to_exclude property
161 END
162
163
164--Checking to see if the database name is specified along wih the table name
165--Your database context should be local to the table for which you want to generate INSERT statements
166--specifying the database name is not allowed
167IF (PARSENAME(@table_name,3)) IS NOT NULL
168 BEGIN
169 RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1)
170 RETURN -1 --Failure. Reason: Database name is specified along with the table name, which is not allowed
171 END
172
173--Checking for the existence of 'user table' or 'view'
174--This procedure is not written to work on system tables
175--To script the data in system tables, just create a view on the system tables and script the view instead
176
177IF @owner IS NULL
178 BEGIN
179 IF ((OBJECT_ID(@table_name,'U') IS NULL) AND (OBJECT_ID(@table_name,'V') IS NULL))
180 BEGIN
181 RAISERROR('User table or view not found.',16,1)
182 PRINT 'You may see this error, if you are not the owner of this table or view. In that case use @owner parameter to specify the owner name.'
183 PRINT 'Make sure you have SELECT permission on that table or view.'
184 RETURN -1 --Failure. Reason: There is no user table or view with this name
185 END
186 END
187ELSE
188 BEGIN
189 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') AND TABLE_SCHEMA = @owner)
190 BEGIN
191 RAISERROR('User table or view not found.',16,1)
192 PRINT 'You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.'
193 PRINT 'Make sure you have SELECT permission on that table or view.'
194 RETURN -1 --Failure. Reason: There is no user table or view with this name
195 END
196 END
197
198--Variable declarations
199DECLARE @Column_ID int,
200 @Column_List varchar(8000),
201 @Column_Name varchar(128),
202 @Start_Insert varchar(786),
203 @Data_Type varchar(128),
204 @Actual_Values varchar(8000), --This is the string that will be finally executed to generate INSERT statements
205 @IDN varchar(128) --Will contain the IDENTITY column's name in the table
206
207--Variable Initialization
208SET @IDN = ''
209SET @Column_ID = 0
210SET @Column_Name = ''
211SET @Column_List = ''
212SET @Actual_Values = ''
213
214IF @owner IS NULL
215 BEGIN
216 SET @Start_Insert = 'INSERT INTO ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'
217 END
218ELSE
219 BEGIN
220 SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'
221 END
222
223
224--To get the first column's ID
225
226SELECT @Column_ID = MIN(ORDINAL_POSITION)
227FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
228WHERE TABLE_NAME = @table_name AND
229(@owner IS NULL OR TABLE_SCHEMA = @owner)
230
231
232
233--Loop through all the columns of the table, to get the column names and their data types
234WHILE @Column_ID IS NOT NULL
235 BEGIN
236 SELECT @Column_Name = QUOTENAME(COLUMN_NAME),
237 @Data_Type = DATA_TYPE
238 FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
239 WHERE ORDINAL_POSITION = @Column_ID AND
240 TABLE_NAME = @table_name AND
241 (@owner IS NULL OR TABLE_SCHEMA = @owner)
242
243
244
245 IF @cols_to_include IS NOT NULL --Selecting only user specified columns
246 BEGIN
247 IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0
248 BEGIN
249 GOTO SKIP_LOOP
250 END
251 END
252
253 IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns
254 BEGIN
255 IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0
256 BEGIN
257 GOTO SKIP_LOOP
258 END
259 END
260
261 --Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
262 IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1
263 BEGIN
264 IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column
265 SET @IDN = @Column_Name
266 ELSE
267 GOTO SKIP_LOOP
268 END
269
270 --Making sure whether to output computed columns or not
271 IF @ommit_computed_cols = 1
272 BEGIN
273 IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsComputed')) = 1
274 BEGIN
275 GOTO SKIP_LOOP
276 END
277 END
278
279 --Tables with columns of IMAGE data type are not supported for obvious reasons
280 IF(@Data_Type in ('image'))
281 BEGIN
282 IF (@ommit_images = 0)
283 BEGIN
284 RAISERROR('Tables with image columns are not supported.',16,1)
285 PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.'
286 PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit column list using @include_column_list=0, the generated INSERTs will fail.'
287 RETURN -1 --Failure. Reason: There is a column with image data type
288 END
289 ELSE
290 BEGIN
291 GOTO SKIP_LOOP
292 END
293 END
294
295 --Determining the data type of the column and depending on the data type, the VALUES part of
296 --the INSERT statement is generated. Care is taken to handle columns with NULL values. Also
297 --making sure, not to lose any data from flot, real, money, smallmomey, datetime columns
298 SET @Actual_Values = @Actual_Values +
299 CASE
300 WHEN @Data_Type IN ('char','varchar','nchar','nvarchar')
301 THEN
302 'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
303 WHEN @Data_Type IN ('datetime','smalldatetime')
304 THEN
305 'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',109))+'''''''',''NULL'')'
306 WHEN @Data_Type IN ('uniqueidentifier')
307 THEN
308 'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'
309 WHEN @Data_Type IN ('text','ntext')
310 THEN
311 'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
312 WHEN @Data_Type IN ('binary','varbinary')
313 THEN
314 'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
315 WHEN @Data_Type IN ('timestamp','rowversion')
316 THEN
317 CASE
318 WHEN @include_timestamp = 0
319 THEN
320 '''DEFAULT'''
321 ELSE
322 'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
323 END
324 WHEN @Data_Type IN ('float','real','money','smallmoney')
325 THEN
326 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ',2)' + ')),''NULL'')'
327 ELSE
328 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ')' + ')),''NULL'')'
329 END + '+' + ''',''' + ' + '
330
331 --Generating the column list for the INSERT statement
332 SET @Column_List = @Column_List + @Column_Name + ','
333
334 SKIP_LOOP: --The label used in GOTO
335
336 SELECT @Column_ID = MIN(ORDINAL_POSITION)
337 FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
338 WHERE TABLE_NAME = @table_name AND
339 ORDINAL_POSITION > @Column_ID AND
340 (@owner IS NULL OR TABLE_SCHEMA = @owner)
341
342
343 --Loop ends here!
344 END
345
346--To get rid of the extra characters that got concatenated during the last run through the loop
347SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)
348SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)
349
350IF LTRIM(@Column_List) = ''
351 BEGIN
352 RAISERROR('No columns to select. There should at least be one column to generate the output',16,1)
353 RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter
354 END
355
356--Forming the final string that will be executed, to output the INSERT statements
357IF (@include_column_list <> 0)
358 BEGIN
359 SET @Actual_Values =
360 'SELECT ' +
361 CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +
362 '''' + RTRIM(@Start_Insert) +
363 ' ''+' + '''(' + RTRIM(@Column_List) + '''+' + ''')''' +
364 ' +''VALUES(''+ ' + @Actual_Values + '+'')''' + ' ' +
365 COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')
366 END
367ELSE IF (@include_column_list = 0)
368 BEGIN
369 SET @Actual_Values =
370 'SELECT ' +
371 CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +
372 '''' + RTRIM(@Start_Insert) +
373 ' '' +''VALUES(''+ ' + @Actual_Values + '+'')''' + ' ' +
374 COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')
375 END
376
377--Determining whether to ouput any debug information
378IF @debug_mode =1
379 BEGIN
380 PRINT '/*****START OF DEBUG INFORMATION*****'
381 PRINT 'Beginning of the INSERT statement:'
382 PRINT @Start_Insert
383 PRINT ''
384 PRINT 'The column list:'
385 PRINT @Column_List
386 PRINT ''
387 PRINT 'The SELECT statement executed to generate the INSERTs'
388 PRINT @Actual_Values
389 PRINT ''
390 PRINT '*****END OF DEBUG INFORMATION*****/'
391 PRINT ''
392 END
393
394PRINT '--INSERTs generated by ''sp_generate_inserts'' stored procedure written by Vyas'
395PRINT '--Build number: 22'
396PRINT '--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com'
397PRINT '--http://vyaskn.tripod.com'
398PRINT ''
399PRINT 'SET NOCOUNT ON'
400PRINT ''
401
402
403--Determining whether to print IDENTITY_INSERT or not
404IF (@IDN <> '')
405 BEGIN
406 PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' ON'
407 PRINT 'GO'
408 PRINT ''
409 END
410
411
412IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)
413 BEGIN
414 IF @owner IS NULL
415 BEGIN
416 SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
417 END
418 ELSE
419 BEGIN
420 SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
421 END
422
423 PRINT 'GO'
424 END
425
426PRINT ''
427PRINT 'PRINT ''Inserting values into ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' + ''''
428
429
430--All the hard work pays off here!!! You'll get your INSERT statements, when the next line executes!
431EXEC (@Actual_Values)
432
433PRINT 'PRINT ''Done'''
434PRINT ''
435
436
437IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)
438 BEGIN
439 IF @owner IS NULL
440 BEGIN
441 SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'
442 END
443 ELSE
444 BEGIN
445 SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'
446 END
447
448 PRINT 'GO'
449 END
450
451PRINT ''
452IF (@IDN <> '')
453 BEGIN
454 PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' OFF'
455 PRINT 'GO'
456 END
457
458PRINT 'SET NOCOUNT OFF'
459
460
461SET NOCOUNT OFF
462RETURN 0 --Success. We are done!
463END
464
465GO
466
467PRINT 'Created the procedure'
468GO
469
470
471--Mark procedure as system object
472EXEC sys.sp_MS_marksystemobject sp_generate_inserts
473GO
474
475PRINT 'Granting EXECUTE permission on sp_generate_inserts to all users'
476GRANT EXEC ON sp_generate_inserts TO public
477
478SET NOCOUNT OFF
479GO
480
481PRINT 'Done'