MD3.ScriptModelData.sql 12.6 KB
IF EXISTS (SELECT * FROM sysobjects WHERE type in ('P') and id = object_id('MD3.ScriptModelData'))
     BEGIN
          DROP PROCEDURE MD3.ScriptModelData
     END
GO

create PROC MD3.ScriptModelData
(
	@table_schema varchar(776),  		-- The table/view schema name
	@table_name varchar(776),  		-- The table/view for which the INSERT statements will be generated using the existing data
	@target_table varchar(776) = NULL, 	-- Use this parameter to specify a different table name into which the data will be inserted
	@include_column_list bit = 1,		-- Use this parameter to include/ommit column list in the generated INSERT statement
	@from varchar(800) = NULL, 		-- Use this parameter to filter the rows based on a filter condition (using WHERE)
	@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
	@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
	@top int = NULL,			-- Use this parameter to generate INSERT statements only for the TOP n rows
	@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
	@disable_constraints bit = 0,		-- When 1, disables foreign key constraints and enables them after the INSERT statements
	@ommit_computed_cols bit = 0		-- When 1, computed columns will not be included in the INSERT statement
	
)
AS
BEGIN


/*

Usage
------------
	This procedure can be called like this, at a minimum:

	EXEC MD3.ScriptModelData
		@table_schema = 'dbo',
		@table_name = 'Table_1'

	Make sure you change isqlw's output to TEXT mode (Ctl+T) since everything is a PRINT statement.

Purpose
------------



*/


SET NOCOUNT ON


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

--Variable Initialization
SET @IDN = ''
SET @Column_ID = 0
SET @Column_Name = ''
SET @Column_List = ''
SET @Update_Column_List = ''
SET @Actual_Values = ''


	BEGIN
		--SET @Start_Insert = 'INSERT INTO ' + '[' + @table_schema + '].' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' 
		SET @Start_Insert = 'INSERT INTO #' + RTRIM(COALESCE(@target_table,@table_name)) 
	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
TABLE_SCHEMA = @table_schema

--assume the first col is the PK
declare @PkColName varchar(200)
SELECT @PkColName = Column_name
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE 	TABLE_NAME = @table_name AND TABLE_SCHEMA = @table_schema AND ORDINAL_POSITION = @Column_ID

--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
		TABLE_SCHEMA = @table_schema



		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(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
		
		--Making sure whether to output computed columns or not
		IF @ommit_computed_cols = 1
		BEGIN
			IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(USER_NAME()) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsComputed')) = 1 
			BEGIN
				GOTO SKIP_LOOP					
			END
		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
					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
		SET @Actual_Values = @Actual_Values  +
		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
		SET @Column_List = @Column_List +  @Column_Name + ','	
		
		--Generating the column list for the UPDATE statement
		SET @Update_Column_List = @Update_Column_List + @Column_Name + ' = temp.' + @Column_Name + ' ,' 

		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
		TABLE_SCHEMA = @table_schema


	--Loop ends here!
	END

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

IF LTRIM(@Column_List) = '' 
	BEGIN
		RAISERROR('No columns to select. There should at least be one column to generate the output',16,1)
		RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter
	END

--Forming the final string that will be executed, to output the INSERT statements
IF (@include_column_list <> 0)
	BEGIN
		SET @Actual_Values = 
			'SELECT ' +  
			CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END + 
			'''' + RTRIM(@Start_Insert) + 
			' ''+' + '''(' + RTRIM(@Column_List) +  '''+' + ''')''' + 
			' +''VALUES(''+ ' +  @Actual_Values  + '+'')''' + ' ' + 
			COALESCE(@from,' FROM ' + '[' + LTRIM(RTRIM(@table_schema)) + '].'  +  '[' + rtrim(@table_name) + ']' + '(NOLOCK)')
	END
ELSE IF (@include_column_list = 0)
	BEGIN
		SET @Actual_Values = 
			'SELECT ' + 
			CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END + 
			'''' + RTRIM(@Start_Insert) + 
			' '' +''VALUES(''+ ' +  @Actual_Values + '+'')''' + ' ' + 
			COALESCE(@from,' FROM ' + '[' + LTRIM(RTRIM(@table_schema)) + '].'  +  '[' + rtrim(@table_name) + ']' + '(NOLOCK)')
	END	



--begin emitting data
PRINT '-------------------------------------------------------------------------------------------------'
PRINT '--To add a row to the table...add a new INSERT line to the file'
PRINT '--To change a row in the table...simply change the row''s INSERT statement'
PRINT '--To delete a row from the table...remove the INSERT line or comment it out.  '
PRINT '--generated from MD3.ScriptModelData'
PRINT '-------------------------------------------------------------------------------------------------'
PRINT 'SET NOCOUNT ON'
PRINT '--temporarily disable foreignkeys '
PRINT 'EXEC MD3.ForeignKeyManipulation @operation = ''DISABLE'' , @tablename = ''' + @table_name + ''' , @schemaName = ''' + @table_schema + ''''
PRINT 'GO'
PRINT ''
PRINT '--build a base temp table'
PRINT 'SELECT ' + @column_list
PRINT 'INTO #' + @table_name
PRINT 'FROM ' + quotename(@table_schema) + '.' + quotename (@table_name)
PRINT 'WHERE 1 = 0'
PRINT 'GO'



EXEC  (@Actual_Values)



--insert new rows
PRINT 'SET NOCOUNT OFF'
PRINT 'PRINT ''Inserting New Rows (if needed) into ' + quotename(@table_schema) + '.' + quotename (@table_name) + ''''
PRINT 'INSERT INTO ' +  quotename(@table_schema) + '.' + quotename (@table_name) 
PRINT '     (' + @Column_List + ')'
PRINT 'SELECT temp.*'
PRINT 'FROM #' + @table_name + ' temp'
PRINT 'LEFT JOIN ' + quotename(@table_schema) + '.' + quotename (@table_name) + ' main'
PRINT '     ON temp.' + @PkColName + ' = main.' + @PkColName
PRINT 'WHERE main.' + @PkColName + ' IS NULL;'
PRINT ''
PRINT ''
PRINT ''

--delete rows no longer needed
PRINT 'PRINT ''Deleting Old Rows (if needed) from ' + quotename(@table_schema) + '.' + quotename (@table_name) + ''''
PRINT 'DELETE FROM ' + quotename(@table_schema) + '.' + quotename (@table_name) 
PRINT 'FROM ' + quotename(@table_schema) + '.' + quotename (@table_name) + ' main'
PRINT 'LEFT JOIN #' + @table_name + ' temp'
PRINT 'ON main.' + @PkColName + ' = temp.' + @PkColName
PRINT 'WHERE temp.' + @PkColName + ' IS NULL;'
PRINT ''
PRINT ''
PRINT ''

--update existing rows
PRINT 'PRINT ''Updating Rows that have changed from ' + quotename(@table_schema) + '.' + quotename (@table_name) + ''''
PRINT 'UPDATE ' + quotename(@table_schema) + '.' + quotename (@table_name) + ' SET '
PRINT @Update_Column_List
PRINT 'FROM ' + quotename(@table_schema) + '.' + quotename (@table_name) + ' main'
PRINT 'JOIN #' + @table_name + ' temp'
PRINT 'ON main.' + @PkColName + ' = temp.' + @PkColName
PRINT 'JOIN	('
PRINT '		SELECT ' + @PkColName + ', BINARY_CHECKSUM(*) AS bin_checksum FROM #' + @table_name
PRINT '		EXCEPT'
PRINT '		SELECT ' + @PkColName + ', BINARY_CHECKSUM(*) AS bin_checksum FROM ' + quotename(@table_schema) + '.' + quotename (@table_name)
PRINT '		) checksm'
PRINT 'ON main.' + @PkColName + ' = checksm.' + @PkColName


PRINT ''	
PRINT 'GO'
PRINT 'DROP TABLE #' + @table_name
PRINT 'GO'
PRINT ''
PRINT ''
PRINT ''



PRINT 'PRINT ''We are not going to re-enable the FKs in this script.  MD3 deployment will do this for us.'''
PRINT 'PRINT ''If you want to manually run this in your DEV env then run this command:'''

PRINT 'PRINT ''EXEC MD3.ForeignKeyManipulation @operation = ENABLE , @tablename = Blah, and @schemaname = Blah'''
PRINT 'GO'

--Determining whether to print IDENTITY_INSERT or not
IF (@IDN <> '')
	BEGIN
		PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@table_schema,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' ON'
		PRINT 'GO'
		PRINT ''
	END



PRINT ''



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

PRINT 'SET NOCOUNT OFF'


SET NOCOUNT OFF
RETURN 0 --Success. We are done!
END
GO