[MSSQL][SP][프로젝트] 테이블에 대한 임시테이블(#Temp Table) 생성

[MSSQL][SP][프로젝트] 테이블에 대한 임시테이블(#Temp Table) 생성

 

- 지정된 테이블에 대한 #TEMP 테이블(임시테이블) 스크립트 생성

-- Common_GetTmpTableFromTable @iTableName='TbDummy' CREATE PROCEDURE [dbo].[Common_GetTmpTableFromTable] @iTableName VARCHAR(50) AS SET NOCOUNT ON DECLARE @wIndex INT DECLARE @wMaxCnt INT DECLARE @wMakeSQL VARCHAR(MAX) DECLARE @wColumns VARCHAR(MAX) SELECT @wIndex = 1 SELECT @wMaxCnt = 0 SELECT @wMakeSQL = '' SELECT @wColumns = '' CREATE TABLE #TEMP1 ( Seq INT IDENTITY(1,1), TableName VARCHAR(50), ColumnName VARCHAR(50), TypeName VARCHAR(50), Size VARCHAR(20) ) INSERT INTO #TEMP1 ( TableName,ColumnName,TypeName,Size ) SELECT OBJECT_NAME(object_id) 테이블,name 컬럼,TYPE_NAME(system_type_id) 타입,max_length FROM sys.columns WHERE OBJECT_NAME(object_id) = @iTableName SELECT @wMaxCnt = COUNT(*) FROM #TEMP1 /*********/ MakeProc: /*********/ SELECT @wMakeSQL = @wMakeSQL + ' CREATE TABLE #TEMP_' + @iTableName + ' ( ' + CHAR(13)+CHAR(10) WHILE (@wIndex<=@wMaxCnt) BEGIN SELECT @wMakeSQL = @wMakeSQL + ' ' + LOWER(ColumnName) + ' ' + LOWER(TypeName) + CASE WHEN TypeName NOT IN ( 'money','int','tinyint','bit','smalldatetime','datetime' ) THEN '(' + Size + ')' ELSE '' END + CASE WHEN @wIndex = @wMaxCnt THEN CHAR(13)+CHAR(10) + ')' ELSE ',' + CHAR(13)+CHAR(10) END FROM #TEMP1 WHERE Seq = @wIndex SELECT @wIndex = @wIndex + 1 END PRINT(@wMakeSQL) RETURN