Script Generator

Create PROCEDURE dbo.usp_GenerateScript

	@table_name varchar(776),  		-- The table/view for which the INSERT statements will be generated using the existing data
	@target_Table varchar(776)=null,	-- Name of target table
	@include_column_list bit=1,		-- Use this parameter to include/ommit column list in the generated INSERT statement
	@Condition varchar(800)='', 		-- Use this parameter to filter the rows based on a filter condition (with WHERE clause)
	@KeyColumns varchar(2000),	        -- Column List for condition, e.g 'state_cd,zip_code'
	@InsertOnly bit=0,			-- If set to 1, it will generate only insert statements else it will generate update and insert both
	@include_timestamp bit=0, 		-- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement
	@debug_mode bit =0,			-- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
	@owner varchar(64)=null ,		-- Use this parameter if you are not the owner of the table
	@ommit_images bit=0,			-- Use this parameter to generate INSERT statements by omitting the 'image' columns
	@ommit_identity bit=0,		-- Use this parameter to ommit the identity columns
	@cols_to_include varchar(8000)=null,	-- List of columns to be included in the INSERT statement
	@cols_to_exclude varchar(8000)=null 	-- List of columns to be excluded from the INSERT statement
AS

SET NOCOUNT ON

--usp_generateScript @table_name = 'zip_lookup',  @include_column_list=0, @Condition= ' where state_cd=''SC''',@keyColumns='zip_code,state_cd', @InsertOnly=null, @ommit_identity=0, @debug_mode=1

SET @InsertOnly = isNUll(@InsertOnly,0)
SET @Condition = isNull(@Condition,'')

--Making sure user only uses either @cols_to_include or @cols_to_exclude
IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL))
	BEGIN
		RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1)
		--RETURN -1 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified
		GOTO quit
	END

--Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format
IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0))
	BEGIN
		RAISERROR('Invalid use of @cols_to_include property',16,1)
		PRINT 'Specify column names surrounded by single quotes and separated by commas'
		PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',''title''"'
		--RETURN -1 --Failure. Reason: Invalid use of @cols_to_include property
		GOTO quit
	END

IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0))
	BEGIN
		RAISERROR('Invalid use of @cols_to_exclude property',16,1)
		PRINT 'Specify column names surrounded by single quotes and separated by commas'
		PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'',''title''"'
		--RETURN -1 --Failure. Reason: Invalid use of @cols_to_exclude property
		GOTO quit
	END


--Checking to see if the database name is specified along wih the table name
--Your database context should be local to the table for which you want to generate INSERT statements
--specifying the database name is not allowed
IF (PARSENAME(@table_name,3)) IS NOT NULL
	BEGIN
		RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1)
		--RETURN -1 --Failure. Reason: Database name is specified along with the table name, which is not allowed
		GOTO quit
	END

--Checking for the existence of 'user table' or 'view'
--This procedure is not written to work on system tables
--To script the data in system tables, just create a view on the system tables and script the view instead

IF @owner IS NULL
	BEGIN
		IF ((OBJECT_ID(@table_name,'U') IS NULL) AND (OBJECT_ID(@table_name,'V') IS NULL)) 
			BEGIN
				RAISERROR('User table or view not found.',16,1)
				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.'
				PRINT 'Make sure you have SELECT permission on that table or view.'
				--RETURN -1 --Failure. Reason: There is no user table or view with this name
				GOTO quit
			END
	END
ELSE
	BEGIN
		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)
			BEGIN
				RAISERROR('User table or view not found.',16,1)
				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.'
				PRINT 'Make sure you have SELECT permission on that table or view.'
				--RETURN -1 --Failure. Reason: There is no user table or view with this name	
				GOTO quit	
			END
	END

SET @KeyColumns = ',' + replace(@KeyColumns,' ','') + ','


--Variable declarations
DECLARE		@Column_ID int, 		
		@Column_List varchar(8000), 
		@Column_Name varchar(128), 
		@Start_Insert varchar(786), 
		@Start_Update varchar(786), 
		@Data_Type varchar(128), 
		@Actual_Values varchar(8000),	--This is the string that will be finally executed to generate INSERT statements
		@Update_Values varchar(8000),   --to hold update string
		@IDN varchar(128),		--Will contain the IDENTITY column's name in the table
		@keyConditionList varchar(8000)

--Variable Initialization
SET @IDN = ''
SET @Column_ID = 0
SET @Column_Name = ''
SET @Column_List = ''
SET @Actual_Values = ''
SET @Update_Values =''
SET @keyConditionList=''
SET @owner= (case rtrim(@owner) WHEN '' THEN NULL ELSE rtrim(@owner) end)

IF @owner IS NULL 
	BEGIN
		SET @Start_Insert = 'INSERT INTO ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' 
		SET @Start_Update = 'UPDATE ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + '] SET ' 
	END
ELSE
	BEGIN
		SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' 
		SET @Start_Update = 'UPDATE ' + '[' + LTRIM(RTRIM(@owner)) + '].' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + '] SET ' 	
	END

--To get the first column's ID

SELECT	@Column_ID = MIN(ORDINAL_POSITION) 	
FROM	INFORMATION_SCHEMA.COLUMNS (NOLOCK) 
WHERE 	TABLE_NAME = @table_name AND
(@owner IS NULL OR TABLE_SCHEMA = @owner)


--Loop through all the columns of the table, to get the column names and their data types
WHILE @Column_ID IS NOT NULL
	BEGIN
		SELECT 	@Column_Name = QUOTENAME(COLUMN_NAME), 
		@Data_Type = DATA_TYPE 
		FROM 	INFORMATION_SCHEMA.COLUMNS (NOLOCK) 
		WHERE 	ORDINAL_POSITION = @Column_ID AND 
		TABLE_NAME = @table_name AND
		(@owner IS NULL OR TABLE_SCHEMA = @owner)



		IF @cols_to_include IS NOT NULL --Selecting only user specified columns
		BEGIN
			IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0 
			BEGIN
				GOTO SKIP_LOOP
			END
		END

		IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns
		BEGIN
			IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0 
			BEGIN
				GOTO SKIP_LOOP
			END
		END

		--Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
		IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1 
		BEGIN
			IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column
				SET @IDN = @Column_Name
			ELSE
				GOTO SKIP_LOOP			
		END
		
		
		
		--Tables with columns of IMAGE data type are not supported for obvious reasons
		IF(@Data_Type in ('image'))
			BEGIN
				IF (@ommit_images = 0)
					BEGIN
						RAISERROR('Tables with image columns are not supported.',16,1)
						PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.'
						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.'
						--RETURN -1 --Failure. Reason: There is a column with image data type
						GOTO quit
					END
				ELSE
					BEGIN
					GOTO SKIP_LOOP
					END
			END

		--Determining the data type of the column and depending on the data type, the VALUES part of
		--the INSERT statement is generated. Care is taken to handle columns with NULL values. Also
		--making sure, not to lose any data from flot, real, money, smallmomey, datetime columns

		DECLARE @tmpValue varchar(8000)

		SET @tmpValue =
		CASE 
			WHEN @Data_Type IN ('char','varchar','nchar','nvarchar') 
				THEN 
					'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
			WHEN @Data_Type IN ('datetime','smalldatetime') 
				THEN 
					'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',109))+'''''''',''NULL'')'
			WHEN @Data_Type IN ('uniqueidentifier') 
				THEN  
					'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'
			WHEN @Data_Type IN ('text','ntext') 
				THEN  
					'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'					
			WHEN @Data_Type IN ('binary','varbinary') 
				THEN  
					'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'  
			WHEN @Data_Type IN ('timestamp','rowversion') 
				THEN  
					CASE 
						WHEN @include_timestamp = 0 
							THEN 
								'''DEFAULT''' 
							ELSE 
								'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'  
					END
			WHEN @Data_Type IN ('float','real','money','smallmoney')
				THEN
					'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ',2)' + ')),''NULL'')' 
			ELSE 
				'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ')' + ')),''NULL'')' 
		END  
		
		--Generating the column list for the INSERT statement
		IF @include_column_list=1
			SET @Column_List = @Column_List +  @Column_Name + ','	


		SET @Actual_Values = @Actual_Values  + @tmpValue  + '+' +  ''',''' + ' + '
		
		IF @InsertOnly=0
		BEGIN
			IF (CHARINDEX(','+ replace(replace(@column_name,'[',''),']','') +',', @KeyColumns)>0)
				BEGIN	
					SET @keyConditionList = @keyConditionList + '''' + @column_name + ' = '' + ' + @tmpValue + '+' +  ''' and ''' + ' + ' 
				END
			ELSE
				BEGIN
					SET @Update_Values =  @Update_Values + '''' + @column_name + ' = '' + ' + @tmpValue + '+' +  ''',''' + ' + '
			END	
		END

		SKIP_LOOP: --The label used in GOTO

		SELECT 	@Column_ID = MIN(ORDINAL_POSITION) 
		FROM 	INFORMATION_SCHEMA.COLUMNS (NOLOCK) 
		WHERE 	TABLE_NAME = @table_name AND 
		ORDINAL_POSITION > @Column_ID AND
		(@owner IS NULL OR TABLE_SCHEMA = @owner)

				
	--Loop ends here!
	END


--To get rid of the extra characters that got concatenated during the last run through the loop
IF len(@Column_List)>1
	SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)
	
IF len(@Actual_Values)>6
	SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)

IF len(@Update_Values)>6
	SET @Update_Values = LEFT(@Update_Values,len(@Update_Values) - 6)
	
IF len(@keyConditionList)>10
	SET @keyConditionList = LEFT(@keyConditionList,len(@keyConditionList) - 10)

PRINT 'SET NOCOUNT ON'
PRINT ''
IF (@IDN <> '')
	BEGIN
		PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' ON'
		PRINT 'GO'
	END


DECLARE @minID int, @maxID int

IF OBJECT_ID('tempdb..#tblInsertvalues') IS NOT NULL
	DROP TABLE #tblInsertvalues


CREATE TABLE #tblInsertvalues(id INT identity, value varchar(8000))


SET @keyConditionList= ' + '' WHERE ' + substring(@keyConditionList,2, len(@keyConditionList))

--generate value string for insert
INSERT INTO #tblInsertvalues
EXEC ('Select ' + @actual_values + ' FROM ' + @table_name + ' ' + @Condition)

IF @InsertOnly=0
BEGIN
	
	IF OBJECT_ID('tempdb..#tblUpdatevalues') IS NOT NULL
		DROP TABLE #tblUpdatevalues

	CREATE TABLE #tblUpdatevalues(id INT identity, value varchar(8000))

	--generate value strings for UPDATE
	INSERT INTO #tblUpdatevalues
	EXEC ('Select ' + @Update_values  + ' '  + @keyConditionList + ''''''''' ' +  ' FROM ' + @table_name + ' ' + @Condition  )
END


SELECT @minid=min(id), @maxID=max(id) FROM #tblInsertvalues

SET @actual_values=''
SET @update_values=''

IF @debug_mode=1
	BEGIN
		PRINT 'PRINT ''Processing Table ' + @table_name + ''''
		PRINT 'PRINT '''''
	END

PRINT ''
WHILE @minid<=@maxID
BEGIN

	IF @InsertOnly=0
	BEGIN
		SELECT @update_values=value FROM #tblUpdatevalues WHERE id=@minID

		IF @debug_mode=1
			PRINT 'PRINT ''UPDATE/INSERT ' + replace(substring(@update_values, CHARINDEX('WHERE', @update_values), len(@update_values)),'''','''''') + ''''	
		PRINT ''

		PRINT 'IF EXISTS (SELECT TOP 1 1 FROM [' + RTRIM(COALESCE(@target_table,@table_name)) + '] ' + substring(@update_values, CHARINDEX('WHERE', @update_values), len(@update_values)) + ')'
		PRINT '	BEGIN'
		PRINT '		' +  @Start_Update
		PRINT '		' +  @update_values 
		
		IF @debug_mode=1
			PRINT '		PRINT ''Updated...'''

		PRINT '		END'
		PRINT 'ELSE'
	END
		SELECT @actual_values=value FROM #tblInsertvalues WHERE id=@minID

		PRINT '	BEGIN' 
		PRINT '		' + @Start_Insert

		IF @include_column_list=1
			PRINT '		(' + @Column_list +')'

		PRINT '		VALUES (' + @actual_values +')' 

		IF @debug_mode=1
			PRINT '		PRINT ''Inserted...'''

		PRINT ' 	END'
		PRINT ''
		PRINT 'PRINT '''''
	
	SET @minID=@minID+1	
End

IF OBJECT_ID('tempdb..#tblInsertvalues') IS NOT NULL
	DROP TABLE #tblInsertvalues


IF OBJECT_ID('tempdb..#tblUpdatevalues') IS NOT NULL
	DROP TABLE #tblUpdatevalues


quit:
PRINT ''
IF (@IDN <> '')
	BEGIN
		PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' OFF'
		PRINT 'GO'
	END

PRINT 'SET NOCOUNT OFF'