SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
IF OBJECT_ID('usp_GenerateInserts', 'P') IS NOT NULL
BEGIN
	DROP PROCEDURE usp_GenerateInserts;
END
GO

CREATE PROCEDURE [dbo].[usp_GenerateInserts]
	@cTableSchema VARCHAR(16)
	, @cTableName VARCHAR(128)
	, @sKey VARCHAR(512)
	, @sOrder VARCHAR(512)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @nColId INT = 0, @sColList NVARCHAR(MAX) = '', @cColName NVARCHAR(512) = ''
		, @sStartIns NVARCHAR(512) = 'INSERT INTO ' + @cTableName
		, @cDataType VARCHAR(128) = '', @sActValues NVARCHAR(MAX) = '';
	DECLARE @tProcessTable AS TABLE (colId INT NOT NULL, colName NVARCHAR(256) NOT NULL, colDataType NVARCHAR(128) NOT NULL);

	SELECT @cTableSchema = CASE WHEN @cTableSchema IS NULL OR @cTableSchema = '' THEN 'dbo' ELSE @cTableSchema END
		, @sStartIns = 'INSERT INTO ' + CASE WHEN @cTableSchema = '' THEN '' ELSE '[' + @cTableSchema + '].' END + '[' + @cTableName + ']';
	
	INSERT INTO @tProcessTable
		SELECT ORDINAL_POSITION, LOWER(COLUMN_NAME), DATA_TYPE
			FROM information_schema.columns (NOLOCK)
			WHERE TABLE_NAME = @cTableName AND TABLE_SCHEMA = @cTableSchema AND DATA_TYPE <> 'image';

	SELECT @nColID = MIN(colId) FROM @tProcessTable;
	
	WHILE @nColID IS NOT NULL
	BEGIN
		SELECT @cColName = colName, @cDataType = colDataType 
			FROM @tProcessTable
			WHERE colId = @nColID

		SET @sActValues = @sActValues + CASE 
			WHEN @cDataType IN ('char','varchar','nchar','nvarchar') THEN 'ISNULL(''N'' + '''''''' + REPLACE(RTRIM(' + @cColName + '),'''''''','''''''''''')+'''''''',''NULL'')'
			WHEN @cDataType IN ('datetime','smalldatetime', 'date', 'datetime2') THEN 'ISNULL('''''''' + RTRIM(CONVERT(char,' + @cColName + ',109))+'''''''',''NULL'')'
			WHEN @cDataType IN ('uniqueidentifier') THEN  	'ISNULL('''''''' + CONVERT(VARCHAR(64),RTRIM(' + @cColName + '))+'''''''',''NULL'')'
			--WHEN @cDataType IN ('text') THEN  'ISNULL('''''''' + REPLACE(RTRIM(CONVERT(VARCHAR(MAX),' + @cColName + ')),'''''''','''''''''''')+'''''''',''NULL'')'					
			--WHEN @cDataType IN ('ntext') THEN  'ISNULL(''N'''''' + REPLACE(RTRIM(CONVERT(NVARCHAR(MAX),' + @cColName + ')),'''''''','''''''''''')+'''''''',''NULL'')'					
			WHEN @cDataType IN ('binary','varbinary') THEN  'ISNULL(RTRIM(CONVERT(VARCHAR,' + 'CONVERT(int,' + @cColName + '))),''NULL'')'  
			WHEN @cDataType IN ('float','real','money','smallmoney')THEN 'ISNULL(LTRIM(RTRIM(' + 'CONVERT(VARCHAR, ' +  @cColName  + ', 2)' + ')),''NULL'')' 
			ELSE 'ISNULL(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @cColName  + ')' + ')),''NULL'')' END   + ' + ' +  ''', ''' + ' + '

		SET @sColList = @sColList +  @cColName + ', ';
		SELECT @nColID = MIN(colId)  
			FROM @tProcessTable
			WHERE colId > @nColID
	END
	
	SELECT @sColList = LEFT(@sColList,LEN(@sColList) - 1)
	IF @sActValues <> '' SELECT @sActValues = LEFT(@sActValues,LEN(@sActValues) - 7)

	DECLARE @cSelect NVARCHAR(1024), @cFrom NVARCHAR(1024)
		, @sProcessInsStr NVARCHAR(MAX) = '', @sExecuteStr NVARCHAR(MAX), @sResultStr NVARCHAR(MAX)
	SELECT @sExecuteStr = 'SELECT @sProcessInsStr = @sProcessInsStr 
		+ ''(''+ ' + @sActValues + '+'')'' + CHAR(13) + '', ''
		FROM ' + RTRIM(@cTableName) + ' WHERE ' + @sKey + ' ORDER BY ' + @sOrder
	EXEC sp_executesql @sExecuteStr, N'@sProcessInsStr NVARCHAR(MAX) OUTPUT', @sProcessInsStr OUTPUT
	
	SELECT @sResultStr = @sStartIns + '(' + @sColList + ')' + CHAR(13) + ' VALUES' + LEFT(@sProcessInsStr, LEN(@sProcessInsStr) - 2)
	SELECT @sResultStr AS InsertString

	SET NOCOUNT OFF
END