SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

USE master;
GO

IF OBJECT_ID(N'dbo.sp_genInsertStmt' , 'P') IS NOT NULL
	DROP PROC sp_genInsertStmt;
GO

/**
author : Doeyull.Kim
e-mail : purumae@gmail.com
created date : 2009-07-14
description : ƯÁ¤ Å×ÀÌºí¿¡ ´ëÇØ INSERT Script¸¦ »ý¼ºÇÕ´Ï´Ù.
return value :
0 = There is no error.
**/

CREATE PROCEDURE dbo.sp_genInsertStmt
	@nvcTableName nvarchar(256),
	@nvcWhereClause nvarchar(max) = N''
AS

SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE
	@intReturnValue int,
	@nvcSchemaName nvarchar(128),
	@nvcColumns nvarchar(max),
	@nvcValues nvarchar(max),
	@intIsIdentity int,
	@nvcStmt nvarchar(max),
	@bitRows bit;

BEGIN TRY
	/**_# ½ºÅ°¸¶ À̸§°ú Å×À̺í À̸§À» ºÐ¸®ÇÕ´Ï´Ù. ½ºÅ°¸¶ À̸§ÀÌ ¾ø´Ù¸é dbo¸¦ ±âº»°ªÀ¸·Î ÇÕ´Ï´Ù.*/
	SET @nvcSchemaName = QUOTENAME(ISNULL(PARSENAME(@nvcTableName, 2), N'dbo'));
	SET @nvcTableName = QUOTENAME(PARSENAME(@nvcTableName, 1));

	/**_# @nvcColumns, @nvcValues, @intIsIdentity¸¦ ÃʱâÈ­ÇÕ´Ï´Ù.*/
	SET @nvcColumns = N'';
	SET @nvcValues = N'';
	SET @intIsIdentity = 0;

	/**_# Å×À̺íÀÇ Ä÷³ Á¤º¸¸¦ °¡Á®¿É´Ï´Ù.*/
	SELECT @nvcColumns = @nvcColumns + N', ' + QUOTENAME(C.[name])
		, @nvcValues = @nvcValues + N' + N'', '' + ' +
			CASE
				WHEN TP.name IN (N'bigint', N'numeric', N'bit', N'smallint', N'decimal', N'int', N'tinyint', N'float', N'real') THEN N'ISNULL(CAST(' + QUOTENAME(C.[name]) + N' AS nvarchar(max)), ''NULL'')'
				WHEN TP.name IN (N'char', N'varchar', N'text', N'uniqueidentifier') THEN N'ISNULL(N'''''''' + ' + N'REPLACE(CAST(' + QUOTENAME(C.[name]) + N' AS nvarchar(max)), '''''''', '''''''''''')' + N' + N'''''''', ''NULL'')'
				WHEN TP.name IN (N'nchar', N'nvarchar', N'ntext', N'xml', N'sql_variant') THEN N'ISNULL(N''N'''''' + ' + N'REPLACE(CAST(' + QUOTENAME(C.[name]) + N' AS nvarchar(max)), N'''''''', N'''''''''''')' + N' + N'''''''', ''NULL'')'
				WHEN TP.name IN (N'smallmoney', N'money', N'date', N'datetimeoffset', N'datetime2', N'smalldatetime', N'datetime', N'time') THEN N'ISNULL(master.sys.fn_varbintohexstr(CAST(' + QUOTENAME(C.[name]) + N' AS varbinary(max))), ''NULL'')'
				WHEN TP.name IN (N'binary', N'varbinary', N'image') THEN N'ISNULL(master.sys.fn_varbintohexstr(CAST(' + QUOTENAME(C.[name]) + N' AS varbinary(max))), ''NULL'')'
				WHEN TP.name IN (N'timestamp') THEN N'ISNULL(CAST(master.sys.fn_varbintohexstr(CAST(' + QUOTENAME(C.[name]) + N' AS varbinary(max))) AS timestamp), ''NULL'')'
 			END
		, @intIsIdentity = @intIsIdentity + C.is_identity
	FROM sys.columns C
		INNER JOIN sys.types TP ON C.user_type_id = TP.user_type_id
	WHERE C.[object_id] = OBJECT_ID(@nvcSchemaName + N'.' + @nvcTableName) AND TP.[name] NOT IN (N'timestamp', N'rowversion')
	ORDER BY C.column_id;

	SET @nvcColumns = STUFF(@nvcColumns, 1, 2, N'');
	SET @nvcValues = STUFF(@nvcValues, 1, 11, N'');

	/**_# ÁÖ¼®À» Ãâ·ÂÇÕ´Ï´Ù.*/
	PRINT N'

-----------------------------------------------------------------------------
-- Table Name : ' + @nvcSchemaName + N'.' + @nvcTableName + N'
-----------------------------------------------------------------------------'

	/**_# UDT, hierarchyid Çü½ÄÀÌ Æ÷ÇÔµÈ Å×À̺íÀÌ¸é ¿¡·¯¸¦ Ãâ·ÂÇÕ´Ï´Ù.*/
	IF @nvcValues IS NULL
	BEGIN
		PRINT N'-- UDT, hierarchyid Çü½ÄÀº Áö¿øÇÏÁö ¾Ê½À´Ï´Ù.';
		RETURN 0;
	END

	/**_# INSERT¹®À» »ý¼ºÇÒ ·¹Äڵ尡 Á¸ÀçÇÏ´ÂÁö È®ÀÎÇÕ´Ï´Ù.*/
	SET @nvcStmt = N'
IF EXISTS (
	SELECT * FROM ' + @nvcSchemaName + N'.' + @nvcTableName + N' ' + @nvcWhereClause + N'
)
	SET @bitRows = 1;
ELSE
	SET @bitRows = 0;'

	EXEC sp_executesql @nvcStmt, N'@bitRows bit OUTPUT', @bitRows = @bitRows OUTPUT;

	IF @bitRows = 0
	BEGIN
		PRINT N'-- NO RECORD!!!';
		RETURN 0;
	END

	/**_# IDENTITY ¼Ó¼ºÀ» °¡Áø Ä÷³ÀÌ ÀÖ´Ù¸é SET IDENTITY_INSERT ONÀ» Ãâ·ÂÇÕ´Ï´Ù.*/
	IF @intIsIdentity = 1
		PRINT N'SET IDENTITY_INSERT ' + @nvcSchemaName + N'.' + @nvcTableName + N' ON;';

	/**_# INSERT¹®À» Ãâ·ÂÇÕ´Ï´Ù.*/
	PRINT N'INSERT ' + @nvcSchemaName + N'.' + @nvcTableName + N' (' + @nvcColumns + N')';

	SET @nvcStmt = N'
DECLARE @i int, @j int, @nvcInsertScript nvarchar(max);
DECLARE @tblTemp table (seq int IDENTITY(1, 1) NOT NULL PRIMARY KEY, stmt nvarchar(max) NOT NULL);

INSERT @tblTemp (stmt)
SELECT N''SELECT '' + ' + @nvcValues + N' FROM ' + @nvcSchemaName + N'.' + @nvcTableName + N' ' + @nvcWhereClause + N';

SELECT @i = 1, @j = @@ROWCOUNT;

WHILE @i <= @j
BEGIN
	SELECT @nvcInsertScript = stmt + CASE WHEN @i < @j THEN N'' UNION ALL'' ELSE N'';'' END FROM @tblTemp WHERE seq = @i;

	EXEC sp_print @nvcInsertScript;

	SET @i = @i + 1;
END';

	EXEC sp_executesql @nvcStmt;

	/**_# IDENTITY ¼Ó¼ºÀ» °¡Áø Ä÷³ÀÌ ÀÖ´Ù¸é SET IDENTITY_INSERT OFF¸¦ Ãâ·ÂÇÕ´Ï´Ù.*/
	IF @intIsIdentity = 1
		PRINT N'SET IDENTITY_INSERT ' + @nvcSchemaName + N'.' + @nvcTableName + N' OFF;';

END TRY

BEGIN CATCH
	SET @intReturnValue = ERROR_NUMBER();
	GOTO ErrorHandler;
END CATCH;

RETURN 0;

ErrorHandler:
IF XACT_STATE() <> 0
	ROLLBACK TRANSACTION;

PRINT ERROR_MESSAGE();
GO

EXEC sp_addextendedproperty N'MS_Description', N'ƯÁ¤ Å×ÀÌºí¿¡ ´ëÇÑ INSERT Script¸¦ »ý¼ºÇÕ´Ï´Ù.', N'schema', N'dbo', N'procedure', N'sp_genInsertStmt'
EXEC sp_addextendedproperty N'MS_Description', N'Å×À̺í À̸§.', N'schema', N'dbo', N'procedure', N'sp_genInsertStmt', N'parameter', N'@nvcTableName'
EXEC sp_addextendedproperty N'MS_Description', N'WHERE Àý. (optional)', N'schema', N'dbo', N'procedure', N'sp_genInsertStmt', N'parameter', N'@nvcWhereClause'
GO

EXEC sp_MS_marksystemobject sp_genInsertStmt;
GO

GRANT EXECUTE ON OBJECT::sp_genInsertStmt TO [public];
GO